[20180829]ora-00054.txt

来源：这里教程网时间：2026-03-03 12:10:28 作者：

[20180829]ora-00054.txt --//以前写的，忘记贴出，现在补上。

--//上午在解决问题时遇到ora-00054错误,导致自己手忙脚乱的,自己心理素质还是存在一些问题. --//就是在问题面前如何保持冷静,实际上开始出现ora-00054错误,是因为应用程序阻塞,一个update语句无法提交. --//解锁后,后面出现错误是我自己输入命令.浪费许多时间. --//自己还是在测试环境模拟错误看看如何解决: 1.环境: SCOTT@book> @ ver1 PORT_STRING VERSION BANNER ------------------- ---------- ---------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production $ oerr ora 54 00054, 00000, "resource busy and acquire with NOWAIT specified or timeout expired" // *Cause: Interested resource is busy. // *Action: Retry if necessary or increase timeout. CREATE PUBLIC DATABASE LINK LOOPBACK CONNECT TO SCOTT IDENTIFIED BY book USING 'localhost:1521/book:DEDICATED'; --//grant EXECUTE ON dbms_lock to scott; CREATE OR REPLACE FUNCTION sleep (seconds IN NUMBER) RETURN NUMBER is BEGIN sys.dbms_lock.sleep(seconds); RETURN seconds; END; / 2.测试1: create table empx as select * from emp; create unique index pk_empx on empx(empno); --//session 1: select empx.*,sleep(2) from empx; --//执行要28秒,有足够的时间切换. --//session 2: SCOTT@book> ALTER INDEX scott.pk_empx INVISIBLE; Index altered. SCOTT@book> ALTER INDEX scott.pk_empx VISIBLE; Index altered. --//可以发现在执行select时,修改索引属性不会出现ora-00054错误. 3.测试2: --//session 1: SCOTT@book> select * from empx where rownum=1 for update ; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 --//session 2: SCOTT@book> ALTER INDEX scott.pk_empx INVISIBLE; ALTER INDEX scott.pk_empx INVISIBLE * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired --//可以发现如果事务没有提交或者回滚,不能修改索引属性,否者出现ora-00054错误. 4.测试3: --//session 1: select empx.*,sleep(2) from empx@loopback; --//session 2: SCOTT@book> ALTER INDEX scott.pk_empx INVISIBLE; Index altered. SCOTT@book> ALTER INDEX scott.pk_empx VISIBLE; Index altered. --//可以发现如果是被当作db link访问,修改索引属性不会出现ora-00054错误. SCOTT@book> @ &r/viewlock SID SERIAL# USERNAME OSUSER MACHINE MODULE LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2 OWNER OBJECT_TYP OBJECT_NAME BLOCK LOCKWAIT ------ ---------- ---------- ---------- ---------- ------------ --------------- ---------- ---------- ---------- ---------- ------ ---------- -------------------- ----- -------------------- 274 7 SCOTT oracle gxqyydg4 SQL*Plus TX Transaction Exclusive None 65565 1652 PUBLIC SYNONYM /bda290b6_HttpSessio No nBindingLi --//OBJECT_NAME='/bda290b6_HttpSessionBindingLi'. 5.测试4: --//session 1: SCOTT@book> select * from empx where rownum=1 for update ; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 --//session 2: SCOTT@book> select * from empx where rownum=1 for update ; --//阻塞挂起!! --//session 3: SCOTT@book> ALTER INDEX scott.pk_empx INVISIBLE; ALTER INDEX scott.pk_empx INVISIBLE * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired --//出现阻塞,一定在该表上有事务出现冲突,修改索引属性一定出现ora-00054错误. 6.如何解决: --//session 3: SCOTT@book> SCOTT@book> @ &r/viewlock SID SERIAL# USERNAME OSUSER MACHINE MODULE LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2 OWNER OBJECT_TYP OBJECT_NAME BLOCK LOCKWAIT ------ ---------- ---------- ---------- ---------- ------------ --------------- ---------- ---------- ---------- ---------- ------ ---------- -------------------- ----- -------------------- 41 9 SCOTT oracle gxqyydg4 SQL*Plus TX Transaction None Exclusive 655368 23170 No 0000000084C35DF0 41 9 SCOTT oracle gxqyydg4 SQL*Plus TM DML(TM) Row-X (SX) None 90677 0 SCOTT TABLE EMPX No 0000000084C35DF0 274 7 SCOTT oracle gxqyydg4 SQL*Plus TX Transaction Exclusive None 655368 23170 Yes 274 7 SCOTT oracle gxqyydg4 SQL*Plus TM DML(TM) Row-X (SX) None 90677 0 SCOTT TABLE EMPX No --//理论kill 掉block='YES'的sid,事务能结束事务就ok了. SCOTT@book> alter system kill session '274,7' immediate ; System altered. SCOTT@book> @ &r/viewlock SID SERIAL# USERNAME OSUSER MACHINE MODULE LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2 OWNER OBJECT_TYP OBJECT_NAME BLOCK LOCKWAIT ------ ---------- ---------- ---------- ---------- ------------ --------------- ---------- ---------- ---------- ---------- ------ ---------- -------------------- ----- -------------------- 41 9 SCOTT oracle gxqyydg4 SQL*Plus TM DML(TM) Row-X (SX) None 90677 0 SCOTT TABLE EMPX No 41 9 SCOTT oracle gxqyydg4 SQL*Plus TX Transaction Exclusive None 65566 1651 PUBLIC SYNONYM /3a957d9d_HttpSessio No nContext --//奇怪又出现一个OBJECT_NAME='/3a957d9d_HttpSessionContext'.要么等这个事务结束,要么kill会话. SCOTT@book> alter system kill session '41,9' immediate ; System altered. SCOTT@book> ALTER INDEX scott.pk_empx INVISIBLE; Index altered. SCOTT@book> ALTER INDEX scott.pk_empx VISIBLE; Index altered. 7.附上脚本: $ cat viewlock.sql column sid format 99999 column username format a10 column osuser format a10 column machine format a10 column lock_type format a15 column mode_held format a10 column mode_requested format a10 column lock_id1 format a10 column lock_id2 format a10 column owner format a6 column object_type format a10 column object_name format a20 column block format a5 column lockwait format a20 SELECT se.SID, se.serial#,se.username, se.osuser, se.machine,se.module, DECODE (lk.TYPE, 'TX', 'TX Transaction', 'TM', 'TM DML(TM)', 'UL', 'PL/SQL User Lock', lk.TYPE) lock_type, DECODE (lk.lmode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR (lk.lmode) ) mode_held, DECODE (lk.request, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR (lk.request) ) mode_requested, TO_CHAR (lk.id1) lock_id1, TO_CHAR (lk.id2) lock_id2, ob.owner, ob.object_type, ob.object_name, DECODE (lk.BLOCK, 0, 'No', 1, 'Yes', 2, 'Global') BLOCK, se.lockwait FROM v$lock lk, dba_objects ob, v$session se WHERE lk.TYPE IN ('TX','TM', 'UL') AND lk.SID = se.SID AND lk.id1 = ob.object_id(+) order by 1; --//注意如果rac系统,不能使用上述脚本.