Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00054: resource busy and acquire with NOWAIT specified (Oracle 10g)
ORA-00054: resource busy and acquire with NOWAIT specified [message #352852] Thu, 09 October 2008 22:41 Go to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Hi,

I am in process of index rebuilding.
I completed 2 indexes rebuilding.

But when i am performing index rebuilding for the 3rd index it gives me the following error.

ALTER INDEX DBS.IDX_1              REBUILD;
Index altered.


ALTER INDEX DBS.IDX_4              REBUILD;
Index altered.

05:34:10 SQL> ALTER INDEX DBS.IDX_2              REBUILD;
ALTER INDEX DBS.IDX_2              REBUILD
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified


Elapsed: 00:00:00.00
05:34:17 SQL>




I serached for the solution.
select * from v$lock where request!=0;

no rows selected.

SELECT object_name, o.object_id FROM USER_OBJECTS o, v$locked_object l
 WHERE o.object_id = l.object_id

no rows selected.

SELECT ID1, ID2, TYPE FROM v$lock WHERE SID = (SELECT SID FROM v$mystat WHERE ROWNUM = 1);

no rows selected.



Then why i am getting such error.
Please guide me.

Thanks.
Re: ORA-00054: resource busy and acquire with NOWAIT specified [message #352853 is a reply to message #352852] Thu, 09 October 2008 22:44 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

ORA-00054

Thumbs Up
Rajuvan.
Re: ORA-00054: resource busy and acquire with NOWAIT specified [message #352862 is a reply to message #352852] Thu, 09 October 2008 23:11 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Could you guide me some more?
Still i could not able to execute the statement.
  SELECT s.SID, s.serial#, p.spid 
FROM 
   v$session s, 
   v$process p 
WHERE 
   s.paddr = p.addr 
AND 
   s.SID IN (SELECT SESSION_ID FROM v$locked_object);

Shows me 1 session


What should i do then?
It is somewhat urgent please.

Thanks in advance.
Re: ORA-00054: resource busy and acquire with NOWAIT specified [message #352870 is a reply to message #352852] Thu, 09 October 2008 23:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try to use ONLINE option.

Regards
Michel
Re: ORA-00054: resource busy and acquire with NOWAIT specified [message #352919 is a reply to message #352852] Fri, 10 October 2008 04:33 Go to previous message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
I used the following technique and it worked for me.
SELECT OBJECT_NAME,OBJECT_ID 
FROM DBA_OBJECTS 
WHERE OBJECT_NAME='DBS' 
AND owner='BASEL' 
AND OBJECT_TYPE='TABLE';

OBJECT_ID
---------
54831

SELECT SESSION_ID FROM v$locked_object WHERE OBJECT_ID='65831';

SESSION_ID
---------
78

SELECT * FROM v$SESSION WHERE SID IN (78)

Then i killed that session on unix command prompt.

kill -9 spid

Then i again tried to rebuild and it works.


Thanks,

[Updated on: Fri, 10 October 2008 04:35]

Report message to a moderator

Previous Topic: algorithms graphs
Next Topic: Maximum value
Goto Forum:
  


Current Time: Fri Dec 09 04:04:08 CST 2016

Total time taken to generate the page: 0.06353 seconds