[20180829]ora-00054.txt

[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系統,不能使用上述指令碼.