Oracle-L: RE: Finding the session causing compile to hang

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Thu, 30 Oct 2003 20:09:24 -0800
Message-ID: <F001.005D51D6.20031030200924@fatcity.com>

The old view v$access can show who is using what.

Waleed

-----Original Message-----
[mailto:Bruce.Reardon_at_comalco.riotinto.com.au] Sent: Thursday, October 30, 2003 7:14 PM To: Multiple recipients of list ORACLE-L

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:diegocutrone_at_yahoo.com.ar] (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: Bruce.Reardon_at_comalco.riotinto.com.au

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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Khedr, Waleed
INET: Waleed.Khedr_at_FMR.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------