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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) 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).
