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

创建索引脚本：
cat /home/oracle/idx.sql

set timing on
alter session set parallel_force_local = true;
alter session set workarea_size_policy=manual; 
alter session set sort_area_size=1073741820; 
alter session set sort_area_retained_size=1073741820; 
alter session set db_file_multiblock_read_count=128;
create index JT_USER.idx_DELETE_STATE on "JT_USER"."RME_OPTIC_TERM"(DELETE_STATE,SUPER_RES_TYPE,OPER_STATUS) parallel 8 nologging;
alter index JT_USER.idx_DELETE_STATE noparallel;
alter index JT_USER.idx_DELETE_STATE logging;

复制

创建索引失败：

SQL> create index JT_USER.idx_DELETE_STATE on "JT_USER"."RME_OPTIC_TERM"(DELETE_STATE,SUPER_RES_TYPE,OPER_STATUS) parallel 8 nologging;
create index JT_USER.idx_DELETE_STATE on "JT_USER"."RME_OPTIC_TERM"(DELETE_STATE,SUPER_RES_TYPE,OPER_STATUS) parallel 8 nologging
                                                   *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

复制

处理办法：kill掉锁表进程。

查询锁表进程：（不仅适用于表，同样适用于存储过程等对象）

Select c.inst_id,c.spid from gv$process c where c.addr in(select b.paddr from gv$session b where b.sid in(select sid from gv$access where object='RME_OPTIC_TERM'));

直接拼接kill代码：
select 'kill -9 '||c.spid,c.inst_id from gv$process c where c.addr in(select b.paddr from gv$session b where b.sid in(select sid from gv$access where object='RME_OPTIC_TERM'));

复制

一般在10秒内释放进程。

再次创建成功：

SQL> @/home/oracle/idx.sql

Session altered.

Elapsed: 00:00:00.00

Session altered.

Elapsed: 00:00:00.01

Session altered.

Elapsed: 00:00:00.00

Session altered.

Elapsed: 00:00:00.00

Session altered.

Elapsed: 00:00:00.01

Index created.

Elapsed: 00:01:57.45

Index altered.

Elapsed: 00:00:00.13

Index altered.

Elapsed: 00:00:21.89

复制