RE: How to determine sessions with invalid package states

From: SHEEHAN, JEREMY <Jeremy.Sheehan_at_fpl.com>
Date: Wed, 6 May 2009 13:36:01 -0400
Message-ID: <8833494F383585499CB855121711D26315CF02CA9E_at_JBXEXVS02.fplu.fpl.com>

I use this to determine if an object is locked and by what session. It would probably work for you if you joined it to dba_objects.

set serveroutput on

ACCEPT locked_obj PROMPT 'Enter name of the Locked Object: '

SELECT SUBSTR(A.OWNER,1,12) OWNER, SUBSTR(A.OBJECT,1,20) OBJECT_NAME, SUBSTR(A.TYPE,1,10) TYPE, A.SID,SUBSTR(S.USERNAME,1,12) USERNAME, SUBSTR(S.OSUSER,1,12) OSUSER,S.STATUS STATUS FROM V$ACCESS A, V$SESSION S
WHERE A.SID = S.SID
AND OBJECT=upper('&locked_obj')
order by STATUS,SID;

HTH, Jeremy
P Consider the environment. Please don't print this e-mail unless you really need to.

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Rumpi Gravenstein Sent: Wednesday, May 06, 2009 1:27 PM
To: oracle-l-freelists
Subject: How to determine sessions with invalid package states

All,

Is it possible to write a query to identify sessions that are holding invalidated package states? We are looking at issues around code migrations. The goal is to only recycle sessions that we know will have problems. Any ideas?

--

Rumpi Gravenstein

--

http://www.freelists.org/webpage/oracle-l Received on Wed May 06 2009 - 12:36:01 CDT