RE: Weird ORA-00060 (deadlock) with pragma autonomous transaction
Yes we have a commit in the procedure and we rollback if an exception is raised. That was one of the first things I looked at. It would have been nice if that was the only problem. - Babette -Original Message- Ryan Sent: Tuesday, September 09, 2003 12:29 AM To: Multiple recipients of list ORACLE-L transaction are you committing in the procedure with the autonomous transaction? do you have an exception block with a rollback? - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, September 09, 2003 12:09 AM > We are getting a strange deadlock problem and I am having trouble > understanding the cause and action. Metalink has not been too useful for > this one. > > We have a procedure eg INS_REC that does an insert into table ABC. The > procedure uses a "pragma autonomous transaction". > > We have a package eg DO_SET_OF_WORK that selects from several tables and > then based on the information selected will call the above procedure, along > with several other procedures that will do inserts or deletes (on DIFFERENT > tables). > > The package does opens a cursor to process all records selected. > > When we use "pragma autonomous transaction" AND commit in the procedure > called, the package dies with an ORA-0060. When the "pragma autonomous > transaction" is removed from the procedure, the package does not get a > deadlock. > > There is only ONE user using this set of tables (in a separate schema from > other users on the system) at the time this is occuring. > > I am puzzled. Another DBA suggested this may be related to FREELISTS, so I > increased them for both the table and indexes that were being reported as > deadlocking but it did not make any difference. > > Anyone have any other suggestions ? > > Thanks > Babette > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Babette Turner-Underwood > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ryan 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Babette Turner-Underwood 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).
RE: Weird ORA-00060 (deadlock) with pragma autonomous
We are on version 8.1.7.4 on the mainframe (OS390 platform) -Original Message- James Howerton Sent: Tuesday, September 09, 2003 10:05 AM To: Multiple recipients of list ORACLE-L What version are you on??? There is a bug in 9.2.0.3, I was getting this error regularly on my rman repository db when an rman resync was run. I've patched to 9.2.0.4 and the error has gone away. ...JIM... >>> [EMAIL PROTECTED] 9/8/03 11:09:27 PM >>> We are getting a strange deadlock problem and I am having trouble understanding the cause and action. Metalink has not been too useful for this one. We have a procedure eg INS_REC that does an insert into table ABC. The procedure uses a "pragma autonomous transaction". We have a package eg DO_SET_OF_WORK that selects from several tables and then based on the information selected will call the above procedure, along with several other procedures that will do inserts or deletes (on DIFFERENT tables). The package does opens a cursor to process all records selected. When we use "pragma autonomous transaction" AND commit in the procedure called, the package dies with an ORA-0060. When the "pragma autonomous transaction" is removed from the procedure, the package does not get a deadlock. There is only ONE user using this set of tables (in a separate schema from other users on the system) at the time this is occuring. I am puzzled. Another DBA suggested this may be related to FREELISTS, so I increased them for both the table and indexes that were being reported as deadlocking but it did not make any difference. Anyone have any other suggestions ? Thanks Babette -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Babette Turner-Underwood 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: James Howerton 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Babette Turner-Underwood 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).
RE: Weird ORA-00060 (deadlock) with pragma autonomous transaction
It is at work. I will bring it home (on floppy) and post it tomorrow. -Original Message- Henry Poras Sent: Tuesday, September 09, 2003 2:39 PM To: Multiple recipients of list ORACLE-L transaction Babette, Could you post the deadlock trace file please. Henry -Original Message- Babette Turner-Underwood Sent: Tuesday, September 09, 2003 12:09 AM To: Multiple recipients of list ORACLE-L We are getting a strange deadlock problem and I am having trouble understanding the cause and action. Metalink has not been too useful for this one. We have a procedure eg INS_REC that does an insert into table ABC. The procedure uses a "pragma autonomous transaction". We have a package eg DO_SET_OF_WORK that selects from several tables and then based on the information selected will call the above procedure, along with several other procedures that will do inserts or deletes (on DIFFERENT tables). The package does opens a cursor to process all records selected. When we use "pragma autonomous transaction" AND commit in the procedure called, the package dies with an ORA-0060. When the "pragma autonomous transaction" is removed from the procedure, the package does not get a deadlock. There is only ONE user using this set of tables (in a separate schema from other users on the system) at the time this is occuring. I am puzzled. Another DBA suggested this may be related to FREELISTS, so I increased them for both the table and indexes that were being reported as deadlocking but it did not make any difference. Anyone have any other suggestions ? Thanks Babette -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Babette Turner-Underwood 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Henry Poras 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Babette Turner-Underwood 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).
RE: Weird ORA-00060 (deadlock) with pragma autonomous transaction
Babette, Could you post the deadlock trace file please. Henry -Original Message- Babette Turner-Underwood Sent: Tuesday, September 09, 2003 12:09 AM To: Multiple recipients of list ORACLE-L We are getting a strange deadlock problem and I am having trouble understanding the cause and action. Metalink has not been too useful for this one. We have a procedure eg INS_REC that does an insert into table ABC. The procedure uses a "pragma autonomous transaction". We have a package eg DO_SET_OF_WORK that selects from several tables and then based on the information selected will call the above procedure, along with several other procedures that will do inserts or deletes (on DIFFERENT tables). The package does opens a cursor to process all records selected. When we use "pragma autonomous transaction" AND commit in the procedure called, the package dies with an ORA-0060. When the "pragma autonomous transaction" is removed from the procedure, the package does not get a deadlock. There is only ONE user using this set of tables (in a separate schema from other users on the system) at the time this is occuring. I am puzzled. Another DBA suggested this may be related to FREELISTS, so I increased them for both the table and indexes that were being reported as deadlocking but it did not make any difference. Anyone have any other suggestions ? Thanks Babette -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Babette Turner-Underwood 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Henry Poras 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).
Re: Weird ORA-00060 (deadlock) with pragma autonomous
What version are you on??? There is a bug in 9.2.0.3, I was getting this error regularly on my rman repository db when an rman resync was run. I've patched to 9.2.0.4 and the error has gone away. ...JIM... >>> [EMAIL PROTECTED] 9/8/03 11:09:27 PM >>> We are getting a strange deadlock problem and I am having trouble understanding the cause and action. Metalink has not been too useful for this one. We have a procedure eg INS_REC that does an insert into table ABC. The procedure uses a "pragma autonomous transaction". We have a package eg DO_SET_OF_WORK that selects from several tables and then based on the information selected will call the above procedure, along with several other procedures that will do inserts or deletes (on DIFFERENT tables). The package does opens a cursor to process all records selected. When we use "pragma autonomous transaction" AND commit in the procedure called, the package dies with an ORA-0060. When the "pragma autonomous transaction" is removed from the procedure, the package does not get a deadlock. There is only ONE user using this set of tables (in a separate schema from other users on the system) at the time this is occuring. I am puzzled. Another DBA suggested this may be related to FREELISTS, so I increased them for both the table and indexes that were being reported as deadlocking but it did not make any difference. Anyone have any other suggestions ? Thanks Babette -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Babette Turner-Underwood 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: James Howerton 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).
Re: Weird ORA-00060 (deadlock) with pragma autonomous transaction
are you committing in the procedure with the autonomous transaction? do you have an exception block with a rollback? - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, September 09, 2003 12:09 AM > We are getting a strange deadlock problem and I am having trouble > understanding the cause and action. Metalink has not been too useful for > this one. > > We have a procedure eg INS_REC that does an insert into table ABC. The > procedure uses a "pragma autonomous transaction". > > We have a package eg DO_SET_OF_WORK that selects from several tables and > then based on the information selected will call the above procedure, along > with several other procedures that will do inserts or deletes (on DIFFERENT > tables). > > The package does opens a cursor to process all records selected. > > When we use "pragma autonomous transaction" AND commit in the procedure > called, the package dies with an ORA-0060. When the "pragma autonomous > transaction" is removed from the procedure, the package does not get a > deadlock. > > There is only ONE user using this set of tables (in a separate schema from > other users on the system) at the time this is occuring. > > I am puzzled. Another DBA suggested this may be related to FREELISTS, so I > increased them for both the table and indexes that were being reported as > deadlocking but it did not make any difference. > > Anyone have any other suggestions ? > > Thanks > Babette > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Babette Turner-Underwood > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ryan 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).
Weird ORA-00060 (deadlock) with pragma autonomous transaction
We are getting a strange deadlock problem and I am having trouble understanding the cause and action. Metalink has not been too useful for this one. We have a procedure eg INS_REC that does an insert into table ABC. The procedure uses a "pragma autonomous transaction". We have a package eg DO_SET_OF_WORK that selects from several tables and then based on the information selected will call the above procedure, along with several other procedures that will do inserts or deletes (on DIFFERENT tables). The package does opens a cursor to process all records selected. When we use "pragma autonomous transaction" AND commit in the procedure called, the package dies with an ORA-0060. When the "pragma autonomous transaction" is removed from the procedure, the package does not get a deadlock. There is only ONE user using this set of tables (in a separate schema from other users on the system) at the time this is occuring. I am puzzled. Another DBA suggested this may be related to FREELISTS, so I increased them for both the table and indexes that were being reported as deadlocking but it did not make any difference. Anyone have any other suggestions ? Thanks Babette -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Babette Turner-Underwood 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).
