Hi,
We're using "Oracle Database 12c Enterprise Edition Release 12.1.0.2.0".
I ran a script to see if I could find it in V$SQL.
The script was the following:
set serveroutput on
DECLARE
v_stuff varchar2(100):='/bin/whoami';
vLine0 VARCHAR2 (3000);
vLine1 VARCHAR2 (3000);
vLine2 VARCHAR2 (3000);
vLine3 VARCHAR2 (3000);
vLine4 VARCHAR2 (3000);
BEGIN
dbms_application_info.set_action(substrb('v_stuff='||v_stuff,1,64));
SLDPROC.parse_clob(SLDPROC.SHELL(v_stuff));
dbms_application_info.set_action(v_stuff);
Select c.sql_id, c.schemaname, c.osuser, c.action, S.SQL_TEXT into vLine0, vLine1, vLine2, vLine3, VLine4 from gv$session c
left join v$SQL s
on c.SQL_ID = s.SQL_ID
WHERE c.service_name NOT LIKE '%BACK%' and c.action is not null and rownum < 2;
dbms_output.put_LINE(vLine0||' '||vLine1||' '||vLine2||' '||vLine3||' '||vLine4 );
exception when others
then
dbms_application_info.set_action(null);
raise;
END;
I wanted to find this script in either V$SQL or in GV$SESSION.
I found it the anonymous block in V$SQL but when I tried to join the SQL_ID to the GV$SESSION view it returned nothing.
However when I run a straight query, it shows up in both V$SQL and GV$SESSIONS.
Why can't I see the anonymous block stuff in GV$SESSIONS when I join the SQL_ID?