RE: Finding the session causing compile to hang

From: Reardon, Bruce (CALBBAY) <Bruce.Reardon_at_comalco.riotinto.com.au>
Date: Thu, 30 Oct 2003 18:04:24 -0800
Message-ID: <F001.005D51CA.20031030180424@fatcity.com>

Yong,

Certainly agree Steve's code is good :-)

I don't think v$sql will give you the same information - it would show the top level actual package being executed, but not those which are called by that package - these do show up in Steve's code.

Regarding the dba_lock_internal code - the only problem appears to be in the name used for the column - any rows returned are actually blocked - is that correct?

Bruce Reardon

NOTICE: This e-mail and any attachments are private and confidential and may contain legally privileged information. If you are not an authorised recipient, the copying or distribution of this e-mail and any attachments is prohibited and you must not read, print or act in reliance on this e-mail or attachments. This notice should not be removed.

-----Original Message-----
Sent: Friday, 31 October 2003 12:34 PM
To: Multiple recipients of list ORACLE-L

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)" <Bruce.Reardon_at_comalco.riotinto.com.au> 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: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). Received on Thu Oct 30 2003 - 20:04:24 CST