ORA-00054

问题

环境：RAC 11.2.0.4 主库

业务批处理任务中，归档数据表报错。

00054, 00000, "resource busy and acquire with NOWAIT specified or timeout expired"
// *Cause:  Interested resource is busy.
// *Action: Retry if necessary or increase timeout.

原因

ora-00054是执行ddl操作时比较常见的错误，其主要原因就是无法获得TM lock。drop table、truncate table要获得独占的exclusive意向锁，只要表格中存在任何形态的TM lock，操作就无法满足，就会报告ora-00054错误。

解决方案

select * from v$lock where type='TM';

select owner,object_name from dba_objects where object_id in(id1);

查找会话并kill掉

模拟报错

create table test as select * from dba_objects;
delete from test where rownum < 2;
--select sid from v$mystat where rownum=1;
select userenv('sid') from dual;

select * from v$lock where sid=50;

会话2

truncate table test;