Hi Oracle Gurus,

I’m banging my head into walls for some time trying to set up queries monitoring DB activity that would show relationship between a PL/SQL code ( anonymous block, function or stored procedure) and the statement issued inside.

For instance if I run the anonymous block :

Declare
  NumRecords NUMBER;
begin
  FOR i IN 1..100000 LOOP
  select count(*) into NumRecords from V$session;  
  END LOOP;
end;

Then I query views such as v$SQL, v$SQLAREA, v$active_session_history and I get 2 records, 1 for…

select count(*)from V$session;  

and 1 for…

declare    NumRecords NUMBER;    begin… 

But I cannot show that the statement was run from the PL block. I cannot find a way to show this in Oracle system views. Any tips to do this would be great.

Thanks in advance for your help

Philippe