Search notes:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
create table tq84_ora_54 (
txt varchar2(10)
);
--
-- Table created.
Session 2:
insert into tq84_ora_54 values ('xyz');
--
-- 1 row created.
As long as session 2 did not commit the
DML on
tq84_ora_54, another session cannot drop the table.
Session 1:
drop table tq84_ora_54;
-- *
-- ERROR at line 1:
-- ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Find the
session that is blocking the table from being dropped:
select
ses.sid,
ses.serial#,
prc.spid
from
v$locked_object lck join
dba_objects obj on lck.object_id = obj.object_id join
v$session ses on lck.session_id = ses.sid join
v$process prc on ses.paddr = prc.addr
where
obj.object_name = 'TQ84_ORA_54';
Session 2:
commit;
--
-- Commit complete.
Session 1:
drop table tq84_ora_54;
--
-- Table dropped.
See also
alter session set ddl_lock_timeout = …;
Force the creation of a trace file when the next ORA-00054 is thrown:
alter system set events '54 trace name errorstack level 3';