Home » SQL & PL/SQL » SQL & PL/SQL » debugging resource busy error ORA-00054 (ORacle 9.2 Unix)
debugging resource busy error ORA-00054 [message #428704] Thu, 29 October 2009 09:29 Go to next message
goo321
Messages: 28
Registered: June 2008
Location: houston
Junior Member
I have been trying to fix errors in my database system.

I added insert from gv$locked_object to debug resource busy errors and deadlock errors in my error log procedure.
I have fixed 3 of these errors. For the most recent error I can not find a conflicting lock for an exchange partition command.

Does anyone know If I should be selecting from another dba lock view?

insert into ERRORS_RESOURCE_BUSY
(ID_FROM_DWHAPPS_AT_JOURNAL_OVO, OBJECT_ID, SID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE,server_id)
select v_seq ,OBJECT_ID,SESSION_ID, ORACLE_USERNAME, OS_USER_NAME,locked_mode, inst_id 
from gv$locked_object;




Thanks,
goo

[Updated on: Fri, 30 October 2009 01:51] by Moderator

Report message to a moderator

Re: debugging resource busy error ORA-00054 [message #428705 is a reply to message #428704] Thu, 29 October 2009 09:37 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
SELECT START_TIME FROM V$TRANSACTION
Re: debugging resource busy error ORA-00054 [message #428709 is a reply to message #428704] Thu, 29 October 2009 10:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the whole code.

Regards
Michel
Re: debugging resource busy error ORA-00054 [message #428717 is a reply to message #428705] Thu, 29 October 2009 11:28 Go to previous messageGo to next message
goo321
Messages: 28
Registered: June 2008
Location: houston
Junior Member
in log procedure
if xText like '%ORA-00054%'  or xText like '%ORA-00060%'  then
begin
  insert into DWH_AUD.ERRORS_RESOURCE_BUSY
  (ID, OBJECT_ID, SID, ORACLE_USERNAME, OS_USER_NAME, 
LOCKED_MODE,server_id)
  select v_seq ,OBJECT_ID,SESSION_ID, ORACLE_USERNAME, 
OS_USER_NAME,locked_mode, inst_id from gv$locked_object;

  commit;
exception when others then
  send_mail('dwh_prod@XX.COM', 'failure in p_journal', SQLERRM);commit;
  end;
end if; 


In code:
   command_to_execute := 'ALTER TABLE XX EXCHANGE PARTITION part_'|| v_find_day_to_process
||' WITH TABLE XX INCLUDING INDEXES WITH VALIDATION';
   P_JOURNAL(88, 'M', command_to_execute, 'DWH', 'PROC_NAME');commit;
   execute immediate command_to_execute;
Exception when others then
   P_JOURNAL(88, 'E', 'Error '|| to_char(SQLCODE) || ': ' || SQLERRM, 'DWH', 'PROC_NAME');commit;

[Updated on: Fri, 30 October 2009 01:50] by Moderator

Report message to a moderator

Re: debugging resource busy error ORA-00054 [message #428727 is a reply to message #428717] Thu, 29 October 2009 11:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If procedure only handle 2 errors why caller calls it for ANY error?
In short remove WHEN OTHERS and replace it by handler for ONLY "BUSY" error and let the other errors raise.

Regards
Michel
Re: debugging resource busy error ORA-00054 [message #428730 is a reply to message #428704] Thu, 29 October 2009 12:05 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Also never ever put a commit in an exception handler. It'll commit stuff that should be rolled back.
If you need to commit actions performed in an exception handler create a procedure/function that does an autonomous_transaction and call that.
Re: debugging resource busy error ORA-00054 [message #428750 is a reply to message #428730] Thu, 29 October 2009 16:26 Go to previous messageGo to next message
goo321
Messages: 28
Registered: June 2008
Location: houston
Junior Member
Michel Cadot wrote on Thu, 29 October 2009 11:56
If procedure only handle 2 errors why caller calls it for ANY error?
In short remove WHEN OTHERS and replace it by handler for ONLY "BUSY" error and let the other errors raise.

The p_journal procedure (should be package) records the text and procedure name into log table. When presenting problem I try to present the simplest case possible.

cookiemonster wrote on Thu, 29 October 2009 12:05
Also never ever put a commit in an exception handler. It'll commit stuff that should be rolled back.
If you need to commit actions performed in an exception handler create a procedure/function that does an autonomous_transaction and call that.

In my defense I am following the existing style. It is in a data warehouse with hours long packages running. I did add autonomous transaction to p_journal.

If this happens again I will add a dbms_lock.sleep(5).
I am becoming the superstitious sort.


Re: debugging resource busy error ORA-00054 [message #428832 is a reply to message #428750] Fri, 30 October 2009 04:59 Go to previous message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
goo321 wrote on Thu, 29 October 2009 21:26

cookiemonster wrote on Thu, 29 October 2009 12:05
Also never ever put a commit in an exception handler. It'll commit stuff that should be rolled back.
If you need to commit actions performed in an exception handler create a procedure/function that does an autonomous_transaction and call that.

In my defense I am following the existing style. It is in a data warehouse with hours long packages running. I did add autonomous transaction to p_journal.



Fair enough but the exisating style is a bug and will need fixing wherever it occurs.
Previous Topic: cursor not fetching long column
Next Topic: challenging one (merged)
Goto Forum:
  


Current Time: Fri Dec 02 18:21:23 CST 2016

Total time taken to generate the page: 0.24412 seconds