--************************
-- ORA-00054 故障处理一例
--************************
最近index job出现失败,于是尝试手动执行试试。收到了ORA-00054的错误消息。
一、错误消息
SQL> ALTER INDEX ESMDBA.ESM_PRCS_ERR_IDX1 rebuild;
ALTER INDEX ESMDBA.ESM_PRCS_ERR_IDX1 rebuild
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
二、查看当前数据库中当前锁定对象
SQL> col OBJECT_NAME for a30
SQL> col username format a20
SQL> set linesize 200
SELECT object_name, s.username, s.sid, s.serial#, p.spid, to_char(s.logon_time,'yyyy-mm-dd hh24:mi:ss') logon_time
FROM v$locked_object l, dba_objects o, v$session s, v$process p
WHERE l.object_id = o.object_id
AND l.session_id = sSQL> 2 3 .sid
AND s.paddr = p.addr; 4 5
OBJECT_NAME USERNAME SID SERIAL# SPID LOGON_TIME
------------------------------ -------------------- ---------- ---------- ------------ -------------------
PLAN_TABLE A505995 622 15849 26577 2011-06-08 03:04:30
ESM_PRCS_ERR SMEVFP 396 60589 8388 2011-06-11 18:24:51
VNDR_FEED_SCHED SMEVFP 467 15860 3119 2011-06-11 22:30:48
BB_RF_VNDR_FEED_RCRD SMEVFP 467 15860 3119 2011-06-11 22:30:48
BB_RF_VNDR_REPSTRY SMEVFP 773 1079 8440 2011-06-11 18:24:55
BB_RF_VNDR_FEED_RCRD_DATA SMEVFP 467 15860 3119 2011-06-11 22:30:48
BB_RF_VNDR_FEED_RCRD_DATA SMEVFP 467 15860 3119 2011-06-11 22:30:48
7 rows selected.
三、查看表对象上信息
SQL> select OWNER,INDEX_NAME,TABLE_NAME,INDEX_TYPE from dba_indexes where INDEX_NAME like '%ESM_PRCS_ERR_IDX1%';
<