'Why does a SQL id show up in only some of the performance views

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;

Ignore the

SLDPROC.parse_clob(SLDPROC.SHELL(v_stuff));

functions. Those are proprietary functions I wrote. You could put any function or proc call there if you want.

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?

Also what could I do in order to see the above Anonymous block in GV$SESSIONS?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source