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)" <[EMAIL PROTECTED]> 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:[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).
