Re: what package/procedure did SQL come from?

> Is there a way to do this before 10.2.0.4?
The only way I could think about is to search through the code: select --+ FULL(s) PARALLEL (s,8)
OBJ#, LINE, SOURCE from sys.source$ s where 1=1 and upper(s.source) like upper('%< good part of SQL you are looking for >%');

I know that it isn't something that you asked for but it might help.

Yury

On Tue, Jun 2, 2009 at 7:58 AM, kyle Hailey <kylelf_at_gmail.com> wrote:
> I want to correlate SQL to the packages and procedures they came from.
> Is there a way to do this before 10.2.0.4?
>
> Starting in 10.2.0.4 this is pretty easy thanks to the fields
>
> PLSQL_ENTRY_OBJECT_ID
> PLSQL_ENTRY_SUBPROGRAM_ID
> PLSQL_OBJECT_ID
> PLSQL_SUBPROGRAM
>
> in v$session and v$active_session_history. A nice output can be put
> out using a script like
>
> http://www.perfvision.com/ash/ashpl2.sql
>
> to give
>
> COUNT(*) SQL_ID calling_code
> --------- -------------
> --------------------------------------------------------------------
> 2 1xxksrhwtz3zf ORDERENTRY.NEWORDER => DBMS_RANDOM.VALUE
> 2 1xxksrhwtz3zf ORDERENTRY.NEWORDER => DBMS_LOCK.SLEEP
> 3 1xxksrhwtz3zf ORDERENTRY.NEWORDER => DBMS_APPLICATION_INFO.SET_ACTION
> 13 1xxksrhwtz3zf ORDERENTRY.NEWORDER
> 76 dw2zgaapax1sg ORDERENTRY.NEWORDER
> 131 75621g9y3xmvd ORDERENTRY.BROWSEANDUPDATEORDERS
> 163 0uuqgjq7k12nf ORDERENTRY.NEWORDER
>
> (the count could be changed to %activity or average active sessions )
>
> Best
> Kyle Hailey
> http://oraclemonitor.com
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Jurijs
+371 29268222 (+2 GMT)
============================================
http://otn.oracle.com/ocm/jvelikanovs.html
--
http://www.freelists.org/webpage/oracle-l