Re: what package/procedure did SQL come from?
- From: Jurijs Velikanovs <j.velikanovs@xxxxxxxxx>
- To: pete@xxxxxxxxxxxxxxxx
- Date: Wed, 3 Jun 2009 11:02:09 +1000
Hi Pete,
100% Agree with you.
On the other hand if an object is wrapped there is not much you can do
to address an issue yourself. The only option you probably have is to
send problem report to a development organization.
I have been working with EBS environments for last few years where
most of the code is opened. We use the source$ (on pre-prod) often.
Mark Bobak posted a excellent solution anyway. If a SQL is in SGA
during an investigation time I would try X$ method next time, if it is
post-problem time research i would try X$ (as it seams a lighter
solution) and if not successful SOURCE$.
Yury
On Wed, Jun 3, 2009 at 12:56 AM, Pete Finnigan <pete@xxxxxxxxxxxxxxxx> wrote:
> 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@xxxxxxxxx> 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
>>> --
>>> //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@xxxxxxxxxxxxxxxx
> 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.
>
>
--
Jurijs
+371 29268222 (+2 GMT)
============================================
http://otn.oracle.com/ocm/jvelikanovs.html
--
//www.freelists.org/webpage/oracle-l
Other related posts:
