Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Link procedure to sql statement in v$sqlarea
You can also compare "LAST_ACTIVE_TIME", "LAST_LOADED_TIME", and "FIRST_LOAD_TIME"
values of pl/sql and sql query entries in v$sql.
If you know the pl/sql code, you can also compare expected execution count of standalone sql statement.
Thanks,
Sai
http://sai-oracle.blogspot.com
From: "Koppelaars, Toon" <T.Koppelaars_at_xxxxxxxxxxxxxxxxxxxx>To: <Ajay_Thotangare_at_xxxxxx>, <oracle-l_at_xxxxxxxxxxxxx>Date: Tue, 2 Oct 2007 22:13:26 +0200
V$sqlarea holds a PARSING_SCHEMA_ID column. This holds the user-id under which the SQL was parsed. If from stored procedure, it holds the user-id from the owner of the stored procedure (assuming auth_id current user was not used for the procedure). If from somebody else (not using procedure), it holds somebody-elses user-id.
Hope this helps
Toon
-----Oorspronkelijk bericht-----
Van: oracle-l-bounce_at_xxxxxxxxxxxxx [mailto:oracle-l-bounce_at_xxxxxxxxxxxxx]Namens
Thotangare, Ajay (GTI)
Verzonden: dinsdag 2 oktober 2007 21:35
Aan: oracle-l_at_xxxxxxxxxxxxx
Onderwerp: Link procedure to sql statement in v$sqlarea
Hi,
I have a question about v$sqlarea. Appreciate your help/inputs on this. Background
e.g.
create or replace procedure PQR as
begin
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 3 ike '%PQR%' 2 BEGIN PQR; END; 1 3 select object_name AS INSIDE_PQR_PRC from user_objects1
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.
Appreciate your help.
regards,
Ajay Thotangare
212-647-4312
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Oct 22 2007 - 01:24:35 CDT
![]() |
![]() |