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  |
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 #352919 is a reply to message #352852] |
Fri, 10 October 2008 04:33  |
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
|
|
|
Goto Forum:
Current Time: Tue Apr 29 14:08:34 CDT 2025
|