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:[EMAIL PROTECTED]] 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: [EMAIL PROTECTED] 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: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] 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: [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).
Recompiling blocked package - locating blocking session
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: Miller, Jay INET: [EMAIL PROTECTED] 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: [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).
RE: Recompiling blocked package - locating blocking session
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: [EMAIL PROTECTED] 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: [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).
