Re: what package/procedure did SQL come from?
From: Pete Finnigan <pete_at_petefinnigan.com>
Date: Tue, 02 Jun 2009 15:56:58 +0100
Message-ID: <4A253DBA.90604_at_petefinnigan.com>
Hi Yury,
> 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:
>
>
>
Date: Tue, 02 Jun 2009 15:56:58 +0100
Message-ID: <4A253DBA.90604_at_petefinnigan.com>
Hi Yury,
That would only work if the code is not wrapped of course..:-(
cheers
Pete
Jurijs Velikanovs wrote:
> Hi Kyle,
>
>> 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 >> >> >>
>
>
>
-- Pete Finnigan Director PeteFinnigan.com Limited Specialists in database security. If you need help to audit or secure an Oracle database, please ask for details of our courses and consulting services Phone: +44 (0)1904 791188 Fax : +44 (0)1904 791188 Mob : +44 (0)7742 114223 email: pete_at_petefinnigan.com site : http://www.petefinnigan.com Registered Office: 9 Beech Grove, Acomb, York, YO26 5LD, United Kingdom Company No : 4664901 VAT No. : 940 6681 14 Please note that this email communication is intended only for the addressee and may contain confidential or privileged information. The contents of this email may be circulated internally within your organisation only and may not be communicated to third parties without the prior written permission of PeteFinnigan.com Limited. This email is not intended nor should it be taken to create any legal relations, contractual or otherwise. -- http://www.freelists.org/webpage/oracle-lReceived on Tue Jun 02 2009 - 09:56:58 CDT