Finding the session causing compile to hang

From: David Turner <turner_at_tellme.com>
Date: Thu, 30 Oct 2003 15:59:24 -0800
Message-ID: <F001.005D51BC.20031030155924@fatcity.com>

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: David Turner
  INET: turner_at_tellme.com

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: ListGuru_at_fatcity.com (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). Received on Thu Oct 30 2003 - 17:59:24 CST