RE: How to determine sessions with invalid package states

I found the attached on Metalink some time ago.  Works good at telling
you who is using what.
 

Dick Goulet 
Senior Oracle DBA 
PAREXEL International 

 

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of SHEEHAN, JEREMY
Sent: Wednesday, May 06, 2009 1:36 PM
To: rgravens@xxxxxxxxx; 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;

 

HTH,

 

Jeremy 

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

 

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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

Other related posts: