?
Solved

ORA-00054

Posted on 2005-05-03
9
Medium Priority
?
2,498 Views
Last Modified: 2009-10-08
Hello,
when I run my script tables.sql, I will get lots of this error messages.

ORA-00054: resource busy and acquire with NOWAIT specified

Yesterday this message hasn't appeared. I don't know what's wrong.

in script is only sequence of alternating drop table table_nameI [cascade constraints]; create table_nameI (...); I = 1...n

Moreover I noticed, that this message appears only for tables deleted with commands drop table table_name cascade constraints;

thanks
0
Comment
Question by:xLeon1
  • 5
  • 3
9 Comments
 
LVL 4

Expert Comment

by:kripa_odba
ID: 13917352


Because ur table has been locked by some other process.... so when ur trying to execute drop table it couldn't able to  acquire a lock on the table..Its not a big problem...

Try the command after a few minutes or enter the command without the NOWAIT keyword.  Or check any other transaction is running on that particular table.... or any uncomitted transaction...... Commit it and run once again the drop table process.....


0
 
LVL 4

Expert Comment

by:kripa_odba
ID: 13917384
If you want to knw more abt oracle locking pls....

Refer to this link......

https://www.indiana.edu/~dbateam/resources/tips/oracle_locking.ppt

its a nice PPT...




0
 

Author Comment

by:xLeon1
ID: 13917631
I think that it has nothing to do with locking. Because all tables are mine. You think that somebody could lock them? I'm not using NOWAIT keyword.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 15

Expert Comment

by:ishando
ID: 13923321
It is to do with locking. Other sessions accessing your tables, whether selecting, inserting, updating, deleting, or whatever, will put locks of different levels on your tables.
Some DDL commands have an implicit NOWAIT - so locks of any type on the objects will result in this error.
0
 
LVL 4

Expert Comment

by:kripa_odba
ID: 13924510
Please go through the link which i posted.....
0
 

Author Comment

by:xLeon1
ID: 13924928
I read that link, but I think that my tables shouldn't be locked. They're mine. But if tables are locked, how do I find out it? How do I unlock them?
0
 
LVL 4

Accepted Solution

by:
kripa_odba earned 260 total points
ID: 13925279
By joining v$lock,v$locked_object,v$process you can find it out.....


You can use this query to find out locked objects and who is locking



SELECT oracle_username USERNAME, owner OBJECT_OWNER,
object_name, object_type, s.osuser,
DECODE(l.block,
  0, 'Not Blocking',
  1, 'Blocking',
  2, 'Global') STATUS,
  DECODE(v.locked_mode,
    0, 'None',
    1, 'Null',
    2, 'Row-S (SS)',
    3, 'Row-X (SX)',
    4, 'Share',
    5, 'S/Row-X (SSX)',
    6, 'Exclusive', TO_CHAR(lmode)
  ) MODE_HELD
FROM v$locked_object v, dba_objects d,
v$lock l, v$session s
WHERE v.object_id = d.object_id
AND (v.object_id = l.id1)
and v.session_id = s.sid
ORDER BY oracle_username, session_id;





Once you find out the session id,and serial you can kill that session using

alter system kill session 'session-id,session-serial'





0
 

Author Comment

by:xLeon1
ID: 13929823
Thanks. Friend told me that it is probably locked because it's impossible to lock it again. So you were right. But we are both very estonished. I solved it temporarily with creating new tables with similar names. Thanks for that script, I will certainly try it, in the time I understand it and will have some time.

thanks
0
 
LVL 4

Expert Comment

by:kripa_odba
ID: 13932612
Thanx....


:)
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses
Course of the Month16 days, 15 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question