RE: How to determine sessions with invalid package states
Date: Wed, 6 May 2009 13:52:05 -0400
I found the attached on Metalink some time ago. Works good at telling you who is using what.
Senior Oracle DBA
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of SHEEHAN, JEREMY
Sent: Wednesday, May 06, 2009 1:36 PM
To: rgravens_at_gmail.com; oracle-l-freelists Subject: RE: How to determine sessions with invalid package states
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;
P Consider the environment. Please don't print this e-mail unless you really need to.
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Rumpi Gravenstein
Sent: Wednesday, May 06, 2009 1:27 PM
Subject: How to determine sessions with invalid package states
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 GravensteinReceived on Wed May 06 2009 - 12:52:05 CDT
- application/octet-stream attachment: whoisusing.pls