Ошибка ora 00054 resource busy and acquire with nowait specified or timeout expired

asked Jan 30, 2011 at 11:59

from here ORA-00054: resource busy and acquire with NOWAIT specified

You can also look up the sql,username,machine,port information and get to the actual process which holds the connection

SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,
S.MACHINE,S.PORT , S.LOGON_TIME,SQ.SQL_FULLTEXT 
FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, 
V$PROCESS P, V$SQL SQ 
WHERE L.OBJECT_ID = O.OBJECT_ID 
AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR 
AND S.SQL_ADDRESS = SQ.ADDRESS;

Please Kill Oracle Session

Use below query to check active session info

SELECT
    O.OBJECT_NAME,
    S.SID,
    S.SERIAL#,
    P.SPID,
    S.PROGRAM,
    SQ.SQL_FULLTEXT,
    S.LOGON_TIME
FROM
    V$LOCKED_OBJECT L,
    DBA_OBJECTS O,
    V$SESSION S,
    V$PROCESS P,
    V$SQL SQ
WHERE
    L.OBJECT_ID = O.OBJECT_ID
    AND L.SESSION_ID = S.SID
    AND S.PADDR = P.ADDR
    AND S.SQL_ADDRESS = SQ.ADDRESS;

kill like

alter system kill session 'SID,SERIAL#';

(For example, alter system kill session '13,36543'

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

There is a very easy work around for this problem.

If you run a 10046 trace on your session (google this… too much to explain). You will see that before any DDL operation Oracle does the following:

LOCK TABLE ‘TABLE_NAME’ NO WAIT

So if another session has an open transaction you get an error. So the fix is… drum roll please. Issue your own lock before the DDL and leave out the ‘NO WAIT’.

Special Note:

if you are doing splitting/dropping partitions oracle just locks the partition.
— so yo can just lock the partition subpartition.

So…
The following steps fix the problem.

1. LOCK TABLE ‘TABLE NAME’; — you will ‘wait’ (developers call this hanging). until the session with the open transaction, commits. This is a queue. so there may be several sessions ahead of you. but you will NOT error out.
2. Execute DDL. Your DDL will then run a lock with the NO WAIT. However, your session has aquired the lock. So you are good.
3. DDL auto-commits. This frees the locks.

DML statements will ‘wait’ or as developers call it ‘hang’ while the table is locked.

I use this in code that runs from a job to drop partitions. It works fine. It is in a database that is constantly inserting at a rate of several hundred inserts/second. No errors.

if you are wondering. Doing this in 11g. I have done this in 10g before as well in the past.

answered Apr 29, 2013 at 21:15

This error happens when the resource is busy. Check if you have any referential constraints in the query. Or even the tables that you have mentioned in the query may be busy. They might be engaged with some other job which will be definitely listed in the following query results:

SELECT * FROM V$SESSION WHERE STATUS = 'ACTIVE'

Find the SID,

SELECT * FROM V$OPEN_CURSOR WHERE SID = --the id

answered Jul 15, 2013 at 8:28

In my case, I was quite sure it was one of my own sessions which was blocking. Therefore, it was safe to do the following:

• I found the offending session with:

  SELECT * FROM V$SESSION WHERE OSUSER='my_local_username';

  The session was inactive, but it still held the lock somehow. Note, that you may need to use some other WHERE condition in your case (e.g. try USERNAME or MACHINE fields).

• Killed the session using the ID and SERIAL# acquired above:

  alter system kill session '<id>, <serial#>';

Edited by @thermz: If none of the previous open-session queries work try this one. This query can help you to avoid syntax errors while killing sessions:

• SELECT 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||''' immediate;' FROM V$SESSION WHERE OSUSER='my_local_username_on_OS'

answered Sep 3, 2013 at 10:27

This happens when a session other than the one used to alter a table is holding a lock likely because of a DML (update/delete/insert). If you are developing a new system, it is likely that you or someone in your team issues the update statement and you could kill the session without much consequence. Or you could commit from that session once you know who has the session open.

If you have access to a SQL admin system use it to find the offending session. And perhaps kill it.

You could use v$session and v$lock and others but I suggest you google how to find that session and then how to kill it.

In a production system, it really depends. For oracle 10g and older, you could execute

LOCK TABLE mytable in exclusive mode;
alter table mytable modify mycolumn varchar2(5);

In a separate session but have the following ready in case it takes too long.

alter system kill session '....

It depends on what system do you have, older systems are more likely to not commit every single time. That is a problem since there may be long standing locks. So your lock would prevent any new locks and wait for a lock that who knows when will be released. That is why you have the other statement ready. Or you could look for PLSQL scripts out there that do similar things automatically.

In version 11g there is a new environment variable that sets a wait time. I think it likely does something similar to what I described. Mind you that locking issues don’t go away.

ALTER SYSTEM SET ddl_lock_timeout=20;
alter table mytable modify mycolumn varchar2(5);

Finally it may be best to wait until there are few users in the system to do this kind of maintenance.

answered Jul 15, 2013 at 19:49

harun ugurharun ugur

1,69818 silver badges17 bronze badges

As mentioned in other answers, this error is caused by concurrent DML operations running in other sessions. This causes Oracle to fail to lock the table for DDL with the default NOWAIT option.

For those without admin permissions in the database or who cannot kill/interrupt the other sessions, you can also precede your DDL operation with:

alter session set DDL_LOCK_TIMEOUT = 30;
--Run your DDL command, e.g.: alter table, etc.

I was receiving this error repeatedly in a database with background jobs doing large insert/update operations, and altering this parameter in the session allowed the DDL to continue after a few seconds of waiting for the lock.

For further information, see the comment from rshdev on this answer, this entry on oracle-base or the official docs on DDL_LOCK_TIMEOUT.

answered Aug 27, 2021 at 18:40

I had this error happen when I had 2 scripts I was running. I had:

• A SQL*Plus session connected directly using a schema user account (account #1)
• Another SQL*Plus session connected using a different schema user account (account #2), but connecting across a database link as the first account

I ran a table drop, then table creation as account #1.
I ran a table update on account #2’s session. Did not commit changes.
Re-ran table drop/creation script as account #1. Got error on the drop table x command.

I solved it by running COMMIT; in the SQL*Plus session of account #2.

answered May 23, 2017 at 19:46

I managed to hit this error when simply creating a table! There was obviously no contention problem on a table that didn’t yet exist. The CREATE TABLE statement contained a CONSTRAINT fk_name FOREIGN KEY clause referencing a well-populated table. I had to:

• Remove the FOREIGN KEY clause from the CREATE TABLE statement
• Create an INDEX on the FK column
• Create the FK

answered Nov 19, 2015 at 15:05

answered Jan 17, 2017 at 20:50

Solution given by Shashi’s link is the best… no needs to contact dba or someone else

make a backup

create table xxxx_backup as select * from xxxx;

delete all rows

delete from xxxx;
commit;

insert your backup.

insert into xxxx (select * from xxxx_backup);
commit;

answered Mar 27, 2013 at 9:23

asked Jan 30, 2011 at 11:59

from here ORA-00054: resource busy and acquire with NOWAIT specified

You can also look up the sql,username,machine,port information and get to the actual process which holds the connection

SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,
S.MACHINE,S.PORT , S.LOGON_TIME,SQ.SQL_FULLTEXT 
FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, 
V$PROCESS P, V$SQL SQ 
WHERE L.OBJECT_ID = O.OBJECT_ID 
AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR 
AND S.SQL_ADDRESS = SQ.ADDRESS;

Please Kill Oracle Session

Use below query to check active session info

SELECT
    O.OBJECT_NAME,
    S.SID,
    S.SERIAL#,
    P.SPID,
    S.PROGRAM,
    SQ.SQL_FULLTEXT,
    S.LOGON_TIME
FROM
    V$LOCKED_OBJECT L,
    DBA_OBJECTS O,
    V$SESSION S,
    V$PROCESS P,
    V$SQL SQ
WHERE
    L.OBJECT_ID = O.OBJECT_ID
    AND L.SESSION_ID = S.SID
    AND S.PADDR = P.ADDR
    AND S.SQL_ADDRESS = SQ.ADDRESS;

kill like

alter system kill session 'SID,SERIAL#';

(For example, alter system kill session '13,36543'

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

There is a very easy work around for this problem.

If you run a 10046 trace on your session (google this… too much to explain). You will see that before any DDL operation Oracle does the following:

LOCK TABLE ‘TABLE_NAME’ NO WAIT

So if another session has an open transaction you get an error. So the fix is… drum roll please. Issue your own lock before the DDL and leave out the ‘NO WAIT’.

Special Note:

if you are doing splitting/dropping partitions oracle just locks the partition.
— so yo can just lock the partition subpartition.

So…
The following steps fix the problem.

1. LOCK TABLE ‘TABLE NAME’; — you will ‘wait’ (developers call this hanging). until the session with the open transaction, commits. This is a queue. so there may be several sessions ahead of you. but you will NOT error out.
2. Execute DDL. Your DDL will then run a lock with the NO WAIT. However, your session has aquired the lock. So you are good.
3. DDL auto-commits. This frees the locks.

DML statements will ‘wait’ or as developers call it ‘hang’ while the table is locked.

I use this in code that runs from a job to drop partitions. It works fine. It is in a database that is constantly inserting at a rate of several hundred inserts/second. No errors.

if you are wondering. Doing this in 11g. I have done this in 10g before as well in the past.

answered Apr 29, 2013 at 21:15

This error happens when the resource is busy. Check if you have any referential constraints in the query. Or even the tables that you have mentioned in the query may be busy. They might be engaged with some other job which will be definitely listed in the following query results:

SELECT * FROM V$SESSION WHERE STATUS = 'ACTIVE'

Find the SID,

SELECT * FROM V$OPEN_CURSOR WHERE SID = --the id

answered Jul 15, 2013 at 8:28

In my case, I was quite sure it was one of my own sessions which was blocking. Therefore, it was safe to do the following:

• I found the offending session with:

  SELECT * FROM V$SESSION WHERE OSUSER='my_local_username';

  The session was inactive, but it still held the lock somehow. Note, that you may need to use some other WHERE condition in your case (e.g. try USERNAME or MACHINE fields).

• Killed the session using the ID and SERIAL# acquired above:

  alter system kill session '<id>, <serial#>';

Edited by @thermz: If none of the previous open-session queries work try this one. This query can help you to avoid syntax errors while killing sessions:

• SELECT 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||''' immediate;' FROM V$SESSION WHERE OSUSER='my_local_username_on_OS'

answered Sep 3, 2013 at 10:27

This happens when a session other than the one used to alter a table is holding a lock likely because of a DML (update/delete/insert). If you are developing a new system, it is likely that you or someone in your team issues the update statement and you could kill the session without much consequence. Or you could commit from that session once you know who has the session open.

If you have access to a SQL admin system use it to find the offending session. And perhaps kill it.

You could use v$session and v$lock and others but I suggest you google how to find that session and then how to kill it.

In a production system, it really depends. For oracle 10g and older, you could execute

LOCK TABLE mytable in exclusive mode;
alter table mytable modify mycolumn varchar2(5);

In a separate session but have the following ready in case it takes too long.

alter system kill session '....

It depends on what system do you have, older systems are more likely to not commit every single time. That is a problem since there may be long standing locks. So your lock would prevent any new locks and wait for a lock that who knows when will be released. That is why you have the other statement ready. Or you could look for PLSQL scripts out there that do similar things automatically.

In version 11g there is a new environment variable that sets a wait time. I think it likely does something similar to what I described. Mind you that locking issues don’t go away.

ALTER SYSTEM SET ddl_lock_timeout=20;
alter table mytable modify mycolumn varchar2(5);

Finally it may be best to wait until there are few users in the system to do this kind of maintenance.

answered Jul 15, 2013 at 19:49

harun ugurharun ugur

1,69818 silver badges17 bronze badges

As mentioned in other answers, this error is caused by concurrent DML operations running in other sessions. This causes Oracle to fail to lock the table for DDL with the default NOWAIT option.

For those without admin permissions in the database or who cannot kill/interrupt the other sessions, you can also precede your DDL operation with:

alter session set DDL_LOCK_TIMEOUT = 30;
--Run your DDL command, e.g.: alter table, etc.

I was receiving this error repeatedly in a database with background jobs doing large insert/update operations, and altering this parameter in the session allowed the DDL to continue after a few seconds of waiting for the lock.

For further information, see the comment from rshdev on this answer, this entry on oracle-base or the official docs on DDL_LOCK_TIMEOUT.

answered Aug 27, 2021 at 18:40

I had this error happen when I had 2 scripts I was running. I had:

• A SQL*Plus session connected directly using a schema user account (account #1)
• Another SQL*Plus session connected using a different schema user account (account #2), but connecting across a database link as the first account

I ran a table drop, then table creation as account #1.
I ran a table update on account #2’s session. Did not commit changes.
Re-ran table drop/creation script as account #1. Got error on the drop table x command.

I solved it by running COMMIT; in the SQL*Plus session of account #2.

answered May 23, 2017 at 19:46

I managed to hit this error when simply creating a table! There was obviously no contention problem on a table that didn’t yet exist. The CREATE TABLE statement contained a CONSTRAINT fk_name FOREIGN KEY clause referencing a well-populated table. I had to:

• Remove the FOREIGN KEY clause from the CREATE TABLE statement
• Create an INDEX on the FK column
• Create the FK

answered Nov 19, 2015 at 15:05

answered Jan 17, 2017 at 20:50

Solution given by Shashi’s link is the best… no needs to contact dba or someone else

make a backup

create table xxxx_backup as select * from xxxx;

delete all rows

delete from xxxx;
commit;

insert your backup.

insert into xxxx (select * from xxxx_backup);
commit;

answered Mar 27, 2013 at 9:23