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
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: yong321_at_yahoo.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 - 19:34:24 CST
