Hi Ajay,There is an X$ structure for mapping the read-only dependencies between stored objects and shared cursors, namely x$kglrd. You can use it to link the x$kglob record for the procedure XYZ to the x$kglcursor record for the select statement as follows ...
select /*+ ordered use_hash(d) use_hash(c) */ -- whatever you want from sys.x$kglob o, sys.x$kglrd d, sys.x$kglcursor c, -- other tables in join where d.kglhdcdr = o.kglhdadr and c.kglhdpar = d.kglrdhdl and -- other predicatesThen all you need to do is to see if both are currently pinned (x$kglpn) by the same session.
@ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.christianity.net.au/ - For all -----Original Message----- Subject: Link procedure to sql statement in v$sqlarea Date: Tue, 2 Oct 2007 15:35:13 -0400 From: "Thotangare, Ajay \(GTI\)" <Ajay_Thotangare@xxxxxx> To: <oracle-l@xxxxxxxxxxxxx>
Hi, I have a question about v$sqlarea. Appreciate your help/inputs on this. Background -----------------I have a procedure named 'XYZ' and inside this procedure I have select statement. When I execute this procedure I can see a entry for procedure 'XYZ' and also a entry for select statement (which is actually inside procedure). How do I know if this select statement has come from this procedure or somebody else fired this statement as standalone (not using procedure)e.g. create or replace procedure PQR asbegin execute immediate 'select object_name AS INSIDE_PQR_PRC from user_objects'; end;/ Procedure created. SQL> exec PQR;PL/SQL procedure successfully completed.SQL> select rownum,sql_text,executions from v$sqlarea where sql_text like '%PQR%'; ROWNUM SQL_TEXT EXECUTIONS ------ ----------------------------------------------------------------- ---------- 1 select rownum,sql_text,executions from v$sqlarea where sql_text l 3ike '%PQR%'2 BEGIN PQR; END; 1 3 select object_name AS INSIDE_PQR_PRC from user_objects 1 3 rows selected.How do I know that statement "select object_name AS INSIDE_PQR_PRC from user_objects" is actually fired from procedure and not from outside.
-- //www.freelists.org/webpage/oracle-l