由于表删除了分区,导致索引失效,重建索引过程中出现ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired报错
SQL> ALTER INDEX RELATE.PK_RELATE_RECEIVE_LOG REBUILD;
ALTER INDEX RELATE.PK_RELATE_RECEIVE_LOG REBUILD
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
由报错得知,资源被占用处于忙碌状态,无法创建索引。
先查询该索引属于哪个表:
SQL> select owner,index_name,table_owner,table_name from dba_indexes where index_name='PK_RELATE_RECEIVE_LOG';
OWNER
--------------------------------------------------------------------------------------------------------------------------------
INDEX_NAME
--------------------------------------------------------------------------------------------------------------------------------
TABLE_OWNER
--------------------------------------------------------------------------------------------------------------------------------
TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
RELATE
PK_RELATE_RECEIVE_LOG
RELATE
RELATE_RECEIVE_LOG
该索引位于RELTE.RELATE_RECEIVE_LOG表上。
查询哪个会话占用了RELATE.RELATE_RECEIVE_LOG表:
SQL> select l.session_id,o.owner,o.object_name
2 from v$locked_object l,dba_objects o
3 where l.object_id=o.object_id AND OBJECT_NAME='RELATE_RECEIVE_LOG';
SESSION_ID
----------
OWNER
--------------------------------------------------------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
7746
RELATE
RELATE_RECEIVE_LOG
session id为7746占用了该表,可通过alter system kill session在数据库层面杀除该会话,也可通过OS层面KILL OSPID的方法,此次处理过程中采用后者,释放资源更快:
SQL> select b.spid,a.sid,a.username,a.program,a.machine,BLOCKING_SESSION from v$session a,v$process b where a.paddr=b.addr and a.sid=7746;
SPID SID
------------------------ ----------
USERNAME
--------------------------------------------------------------------------------
PROGRAM
------------------------------------------------
MACHINE
----------------------------------------------------------------
BLOCKING_SESSION
----------------
22448 7746
RELATE
JDBC Thin Client
SPID SID
------------------------ ----------
USERNAME
--------------------------------------------------------------------------------
PROGRAM
------------------------------------------------
MACHINE
----------------------------------------------------------------
BLOCKING_SESSION
----------------
jdbcclient
SQL> !ps -ef | grep 22448
oracle 22448 1 9 Feb01 ? 16-18:35:55 oracleeifdb2 (LOCAL=NO)
oracle 68252 67718 0 11:40 pts/1 00:00:00 /bin/bash -c ps -ef | grep 22448
oracle 68255 68252 0 11:40 pts/1 00:00:00 grep 22448
SQL> !kill -9 22448
SQL> !ps -ef | grep 22448
oracle 1341 67718 0 11:48 pts/1 00:00:00 /bin/bash -c ps -ef | grep 22448
oracle 1343 1341 0 11:48 pts/1 00:00:00 grep 22448
再次执行索引重建:
SQL> ALTER INDEX RELATE.PK_RELATE_RECEIVE_LOG REBUILD;
Index altered.
SQL>