| Record remains locked even after DB server restart [message #650540] |
Wed, 27 April 2016 06:30  |
chat2raj.s
Messages: 163
Registered: October 2010
Location: Chennai, India
|
Senior Member |
|
|
Dear Sir,
We face a peculiar case in our production environment of ERP system where a particular record remains locked even after trying to restart the Data base server.
We try to delete / edit that particular record from Forms / toad and system says record has been locked by one another instance.
Our Senior DBAs had tried their best and couldn't fix it.
Recently when we encountered the same issue, then our DBA solved finally using the FLUSH command at the oracle instance level.
What could be the reason for the same and how to fix it ?
BR
|
|
|
|
|
|
| Re: Record remains locked even after DB server restart [message #650545 is a reply to message #650541] |
Wed, 27 April 2016 06:52  |
chat2raj.s
Messages: 163
Registered: October 2010
Location: Chennai, India
|
Senior Member |
|
|
Thank you Michel.
Yes we use RAC DB. Could not attach Screenshot as the insert an image icon is not functioning correctly.
Where i am trying to edit the record directly in toad (After DB restart) i get error as ORA-00054 Resource busy acquired with no wait.
When i try to update the record in SQL *Plus, there is no error instead it keeps trying to update without result.
|
|
|
|
|
|
|
|
|
|
| Re: Record remains locked even after DB server restart [message #650554 is a reply to message #650553] |
Wed, 27 April 2016 08:47 |
cookiemonster
Messages: 13959
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You can use this query (courtesy of Tom Kyte) to see which session is holding the lock:
select
(select username from v$session where sid=a.sid) blocker,
a.sid,
' is blocking ',
(select username from v$session where sid=b.sid) blockee,
b.sid
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
