Home » SQL & PL/SQL » SQL & PL/SQL » ORA-30006 Resource Busy, acquired with Wait timeout expired (Oracle 10g, Forms 6i)
ORA-30006 Resource Busy, acquired with Wait timeout expired [message #658283] Thu, 08 December 2016 03:55 Go to next message
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
Senior Member
For generating document numbers, the cursor is using FOR UPDATE OF <column> WAIT 5; Now when some other session is trying to generate the document number am getting the error ORA-30006 Resource Busy, acquired with Wait timeout expired.

Now how can i identify, who exactly is holding the lock ? i tried various queries from internet, but didnt help.

1. How can find who is holding the session, to see what exactly is happening in his system.
2. How should i handle this error message for the new user who is trying to generate his own document number
Re: ORA-30006 Resource Busy, acquired with Wait timeout expired [message #658284 is a reply to message #658283] Thu, 08 December 2016 03:56 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
What queries did you try?
Re: ORA-30006 Resource Busy, acquired with Wait timeout expired [message #658286 is a reply to message #658284] Thu, 08 December 2016 04:18 Go to previous messageGo to next message
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
Senior Member
I found the session finally using the following query. But i am interested to know, how actually this situation should have been handled by the programmer.

Scenario : User trying to create a new transaction, where the document number has been fetched from the cursor, but he forgets to save the transaction. So Now the other user is trying to create his own transaction, but will end up with ORA-30006. How to handle this scenario.

SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,
S.MACHINE,  S.LOGON_TIME,SQ.SQL_FULLTEXT 
FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, 
V$PROCESS P, V$SQL SQ 
WHERE L.OBJECT_ID = O.OBJECT_ID 
AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR 
AND OBJECT_NAME LIKE 'OM_TXN_DOC%'
AND S.SQL_ADDRESS = SQ.ADDRESS;
Re: ORA-30006 Resource Busy, acquired with Wait timeout expired [message #658288 is a reply to message #658286] Thu, 08 December 2016 04:45 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well why is the user trying to create a new transaction?
That would normally require a new session.
What happened to the old session?
Re: ORA-30006 Resource Busy, acquired with Wait timeout expired [message #658289 is a reply to message #658286] Thu, 08 December 2016 04:50 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I think you are asking what to do if a session is locking rows and not committing the transaction in a timely fashion? If you have Enterprise Edition licences, you can use the Resource Manager to set a limit on the time a blocking session is allowed to be idle before it gets terminated.
Re: ORA-30006 Resource Busy, acquired with Wait timeout expired [message #658290 is a reply to message #658289] Thu, 08 December 2016 04:55 Go to previous messageGo to next message
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
Senior Member
Cookiemonster, the new session is tried by a new user and not by the same user.

John Watson, Yes!! your suggestion should really help me to overcome this situation.

Thanks to both of you.
Re: ORA-30006 Resource Busy, acquired with Wait timeout expired [message #658291 is a reply to message #658290] Thu, 08 December 2016 04:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Now the question is why "WAIT 5" if you don't know what to do if the time is exhausted?

Re: ORA-30006 Resource Busy, acquired with Wait timeout expired [message #658293 is a reply to message #658291] Thu, 08 December 2016 05:04 Go to previous messageGo to next message
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
Senior Member
May be the session which was locking the table could release and so the successive session can take the control (in case it happens within 5 seconds) before the timeout expired message
Re: ORA-30006 Resource Busy, acquired with Wait timeout expired [message #658295 is a reply to message #658293] Thu, 08 December 2016 05:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This does not answer my question;
1/ You use WAIT 5
2/ You don't know what to do when the 5 seconds expired
The question is: what do you then use WAIT 5?

Re: ORA-30006 Resource Busy, acquired with Wait timeout expired [message #658297 is a reply to message #658295] Thu, 08 December 2016 05:17 Go to previous messageGo to next message
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
Senior Member
Yes, i dont know what to do and system by itself throws ORA-30006 after 5 seconds. The things is, system dosen't throw this error message soon after it found that the table is locked, rather it would wait for 5 seconds and either it may succeed in gaining access to the table or it will throw ORA-30006 after 5 seconds.

By this the number of ORA-30006 errors in a day is likely to come down.
Re: ORA-30006 Resource Busy, acquired with Wait timeout expired [message #658299 is a reply to message #658297] Thu, 08 December 2016 06:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you don't know what to do with the a feature why do you use it?

Re: ORA-30006 Resource Busy, acquired with Wait timeout expired [message #658301 is a reply to message #658299] Thu, 08 December 2016 06:29 Go to previous messageGo to next message
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
Senior Member
Hope my previous remarks clearly explains why i used WAIT 5;
Re: ORA-30006 Resource Busy, acquired with Wait timeout expired [message #658303 is a reply to message #658301] Thu, 08 December 2016 06:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No it does not, it just explains why some day you won't have to care about this error as maybe it won't happen.
Just remove it and it won't happen since now.

Re: ORA-30006 Resource Busy, acquired with Wait timeout expired [message #658304 is a reply to message #658303] Thu, 08 December 2016 06:35 Go to previous message
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
Senior Member
Ok Let me check that too.
Previous Topic: Trigger on Materialized View Log tables
Next Topic: create trigger error in SQL
Goto Forum:
  


Current Time: Thu Mar 28 14:46:09 CDT 2024