Hi, Bruce, Steve Adams' code is based on x$kglpn (librarycache pin), which is correct. But the code based on dba_lock_internal blindly assumes id1 is the object name. There's a similar common misinterpretation; many DBAs assume v$lock.id1 is the object ID, which is only true for type = 'TM' (or maybe several other types).
This info *is* in dba_lock_internal, but the script below just needs a type predicate in the WHERE clause. A simpler solution may be just look at v$sql where users_executing > 0 for your package or procedure (shown in sql_text column). Although v$open_cursor could also be used, I don't think a row showing in there always indicates a library cache pin (executing) on the object. Yong Huang --- "Reardon, Bruce (CALBBAY)" <[EMAIL PROTECTED]> wrote: > David, > You could use Steve Adam's script Executing_packages.sql at > http://www.ixora.com.au/scripts/misc.htm to see what packages are executing. > > More generally, use dba_lock_internal to look at what is being blocked: > > based on Oracle-L script by Diego Cutrone [mailto:[EMAIL PROTECTED] > (Friday, 29 August 2003 7:54 AM) > > COLUMN lock_id2 FORMAT A30 > > select to_char(SESSION_ID,'999') sid , > substr(LOCK_TYPE,1,30) Type, > substr(lock_id1,1,45) Object_Name, > substr(mode_held,1,4) HELD, > substr(mode_requested,1,4) REQ, > lock_id2 lock_addr > FROM dba_lock_internal > WHERE > mode_requested <> 'None' > and mode_requested <> mode_held > ; > > and use inverse of this with a given object_name to find who has the internal > locks. > > HTH, > Bruce Reardon > > -----Original Message----- > Sent: Friday, 31 October 2003 10:59 AM > > I need to figure out a way to see if a procedure is running before attempting > a compile > and I can't figure out what tables to look in. Here's a test I set up > > create or replace procedure sleep(i_val number) > is > > begin > dbms_lock.sleep(i_val); > end; > / > > exec sleep(60); > > > I then check v$lock, v$access, dba_locks(9i I think only) and can't seem to > spot > the sleep stored procedure or it's session. Of course I could look in > v$session and > see it in this example but in a stored procedure that has more to it you will > only see > the current step it is at in the procedure and not the procedure itself. > > I'm trying to be able to identify sessions that hold the lock/latch on a > stored procedure > so I can kill them when sometimes the session is disconnected and just hangs. > > Thx, Dave __________________________________ Do you Yahoo!? Exclusive Video Premiere - Britney Spears http://launch.yahoo.com/promos/britneyspears/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
