Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

User_KWXZ9Jun 20 2013 — edited Jun 20 2013

Hi All ,

I am using oracle 11.2.0.3 with 3 node rac with ODI .

I have the below table which i am not able to drop  and giving the error .

SQL> drop table I$_CDS_FAULT_DEV1;

drop table I$_CDS_FAULT_DEV1

           *

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

SQL> show parameter ddl_lock

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

ddl_lock_timeout                     integer     300

i changed to 100 but still the same error also i have checked and there are no locks . Can you please provide me a way to resolve this issue .

Regards ,

DBApps
This post has been answered by unknown-698157 on Jun 20 2013
Jump to Answer

Comments

DK2010

Hi,

It means  some session accessing that table. you can Query from v$access

Find that sid and kill that sid  and drop the table

HTH

unknown-698157
Answer

For sure there are locks, as the table is in use.

Drop table wants an exclusive lock on the corresponding dictionary record(s).

Whenever the table is in use there will be a row-share lock on that record in the dictionary.

Did you consult gv$access ?

Please do so now.

-------------

Sybrand Bakker

Senior Oracle DBA

Marked as Answer by User_KWXZ9 · Sep 27 2020
1 person found this helpful
Niket Kumar

http://abeytom.blogspot.in/2012/08/finding-and-fixing-ora-00054-resource.html

User_KWXZ9

Sybrand ,

Thanks alot for your help . gv$access resolved the issue .

Regards

DBApps

1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 18 2013
Added on Jun 20 2013
#general-database-discussions
4 comments
79,861 views