ORA00054: resource busy and acquire with NOWAIT specified or timeout expired

数据运维 2024-04-08 泡泡手机阅读

创建索引脚本：
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'));

ORA00054: resource busy and acquire with NOWAIT specified or timeout expired-每日运维

一般在10秒内释放进程。