RE: Recompiling blocked package - locating blocking session

Actually, I think the executing_packages script should be exactly what I need.

Thank you very much. I had browsed ixora but didn't make the connection between this script and what I was looking for.

Jay Miller

-----Original Message-----
[mailto:Bruce.Reardon_at_comalco.riotinto.com.au] Sent: Tuesday, July 23, 2002 11:03 PM
To: Multiple recipients of list ORACLE-L

Jay,

Don't know for sure but you could try Ixora's script to show executing packages available at http://www.ixora.com.au/scripts/misc.htm

You could also look for blocked "internal" locks - try the following:

select * from dba_lock_internal
where

       ( mode_held = 'Null' OR mode_held = 'None' )    AND ( mode_requested <> 'None' )
;

dba_lock_internal is created by catblock.sql but blocks that are in there do not show up in dba_blockers.

>From catblock.sql for dba_lock_internal " * NOTE: This view can be very,
very slow depending on the size of your

• shared pool area and database activity." We haven't found this an issue on (low concurrent load) database.

HTH,
Bruce Reardon

-----Original Message-----
Sent: Wednesday, 24 July 2002 1:24

Had a problem this morning where a package was invalid and it would hang when we tried to recompile.

We assumed that some other session was trying to recompile it but was hanging for some reason.

I have plenty of ways to look at table locks but don't have a query to show locked packages. Frantic searching through the index of the SQL & PL/SQL Annotated Archives didn't help. I ended up searching the Active sessions that looked likely and killing them (and needing to do a kill -9 on the OS level as well) until I was able to recompile the package.

Does anyone have a query that will save the random searching next time?

Thanks,
Jay Miller

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Reardon, Bruce (CALBBAY)
  INET: Bruce.Reardon_at_comalco.riotinto.com.au

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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.com
-- 
Author: Miller, Jay
  INET: JayMiller_at_TDWaterhouse.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).