ORA-30006 Resource Busy, acquired with Wait timeout expired [message #658283] |
Thu, 08 December 2016 03:55 |
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 #658286 is a reply to message #658284] |
Thu, 08 December 2016 04:18 |
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 #658297 is a reply to message #658295] |
Thu, 08 December 2016 05:17 |
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.
|
|
|
|
|
|
|