Home » RDBMS Server » Server Administration » Record remains locked even after DB server restart (Oracle 10g, Forms 6i/10g)
Record remains locked even after DB server restart [message #650540] Wed, 27 April 2016 06:30 Go to next message
chat2raj.s
Messages: 104
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 #650541 is a reply to message #650540] Wed, 27 April 2016 06:37 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ Unless it is a RAC database or a distributive transaction (that is is across several databases) it is not possible a row remains locked, in Oracle meaning, when instance is down.
2/ What do you mean by "lock"?

Use SQL*Plus and copy and paste the session showing what you mean.

Re: Record remains locked even after DB server restart [message #650545 is a reply to message #650541] Wed, 27 April 2016 06:52 Go to previous messageGo to next message
chat2raj.s
Messages: 104
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 #650550 is a reply to message #650545] Wed, 27 April 2016 08:24 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You don't need any image: copy and paste it as text inline.
Please read How to use [code] tags and make your code easier to read.

Don't use TOAD, use SQL*Plus.

[Updated on: Wed, 27 April 2016 08:25]

Report message to a moderator

Re: Record remains locked even after DB server restart [message #650552 is a reply to message #650550] Wed, 27 April 2016 08:39 Go to previous messageGo to next message
chat2raj.s
Messages: 104
Registered: October 2010
Location: Chennai, India
Senior Member
When i use SQL * Plus to update the record, there is no action and system just keeps trying to update for a longer time.
Re: Record remains locked even after DB server restart [message #650553 is a reply to message #650552] Wed, 27 April 2016 08:40 Go to previous messageGo to next message
chat2raj.s
Messages: 104
Registered: October 2010
Location: Chennai, India
Senior Member
Screenshot attached for Reference from Toad
Re: Record remains locked even after DB server restart [message #650554 is a reply to message #650553] Wed, 27 April 2016 08:47 Go to previous messageGo to next message
cookiemonster
Messages: 12990
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;
Re: Record remains locked even after DB server restart [message #650555 is a reply to message #650552] Wed, 27 April 2016 08:50 Go to previous messageGo to next message
BlackSwan
Messages: 25795
Registered: January 2009
Location: SoCal
Senior Member
chat2raj.s wrote on Wed, 27 April 2016 06:39
When i use SQL * Plus to update the record, there is no action and system just keeps trying to update for a longer time.



use COPY & PASTE to show us what exactly you do & how Oracle responds
Re: Record remains locked even after DB server restart [message #650556 is a reply to message #650555] Wed, 27 April 2016 09:06 Go to previous messageGo to next message
chat2raj.s
Messages: 104
Registered: October 2010
Location: Chennai, India
Senior Member
Firstly, there was no session holding the lock when i used the suggested query.
Re: Record remains locked even after DB server restart [message #650557 is a reply to message #650556] Wed, 27 April 2016 09:07 Go to previous messageGo to next message
chat2raj.s
Messages: 104
Registered: October 2010
Location: Chennai, India
Senior Member
Screenshot attached for above query and CMD prompt action with no result
Re: Record remains locked even after DB server restart [message #650558 is a reply to message #650557] Wed, 27 April 2016 10:16 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

NO SCREENSHOT! SQL*PLUS TEXT, COPY AND PASTE.

Re: Record remains locked even after DB server restart [message #650559 is a reply to message #650557] Wed, 27 April 2016 10:16 Go to previous message
cookiemonster
Messages: 12990
Registered: September 2008
Location: Rainy Manchester
Senior Member
Try tracing the session that is stuck doing the update and see what the trace file says it's waiting on.
Previous Topic: Oraclexe - file 1 needs media recovery
Next Topic: Can we upgrade an os of an existing db installation to one that is not certified?
Goto Forum:
  


Current Time: Thu Jan 18 17:00:19 CST 2018

Total time taken to generate the page: 0.01708 seconds