8月20日
1.現場狀態
時間：2012年8月20日
資料庫版本：Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
作業系統平臺：HP-UX
告警日誌： more alert_mdsoss.log
2.定位問題
報錯現象：
ORA-00054: resource busy acquire with nowait specified       資源忙
協成日誌報錯：SQL ERR ：insert into table GNTCPCNN12082015    不能插入表
3.分析原因
一般像這種情況可能是這個表正在被使用，有可能有lock暫時不能使用，在dba許可權下檢視一下是否有v$locked_object，有的話把session幹掉

SQL> select * from v$locked_object;

XIDUSN XIDSLOT    XIDSQN  OBJECT_ID SESSION_ID ORACLE_USERNAME  OS_USER_NAME PROCESS  LOCKED_MODE
---------- ---------- ---------- ---------- ---------- ---------------- --------------- --------------------
      13       13    681477 79913476       855 MDSOSS       mdsdb2       12897             6
      7       41    600151 79914479       998 MDSOSS          npmuser       10468             3
      7       41    600151 79914483       998 MDSOSS          npmuser       10468             3
      7       41    600151 79914481       998 MDSOSS          npmuser       10468             3
      19       27    701115 79913681    1032 MDSOSS       mdsdb3       8658             6
      61       30    182840    153240       1059 MDSOSS       oracle          12738             3
      61       30    182840       212          1059 MDSOSS       oracle          12738             3
      61       30    182840       165          1059 MDSOSS       oracle          12738             3
      30       7    545652 79915059       1073 MDSOSS       npmuser       20387             3
      52       39    385940 79913551    1085 MDSOSS       mdsdb3       12960             6
      51       17    461665 79914748    1090 MDSOSS       npmuser       15454             3
      51       17    461665 79914756    1090 MDSOSS       npmuser       15454             3
      51       17    461665 79914763    1090 MDSOSS       npmuser       15454             3
      58       24    262815 79914375    1175 MDSOSS       npmuser       6369             3
      58       24    262815 79914380    1175 MDSOSS       npmuser       6369             3
      58       24    262815 79914378    1175 MDSOSS       npmuser       6369             3
      10       46    680893 79913459    1179 MDSOSS       mdsdb2       2581             6

17 rows selected

SQL>  select * from v$locked_object;

XIDUSN XIDSLOT    XIDSQN  OBJECT_ID SESSION_ID ORACLE_USERNAME  OS_USER_NAME PROCESS  LOCKED_MODE
---------- ---------- ---------- ---------- ---------- ---------------- --------------- --------------------
      42       22    507847 79915291       976 MDSOSS          npmuser       25324             3
      42       22    507847 79915263       976 MDSOSS          npmuser       25324             3
      42       22    507847 79915282       976 MDSOSS          npmuser       25324             3
      1       5    650009 79915671       989 MDSOSS          npmuser       1175             3
      1       5    650009 79915675       989 MDSOSS          npmuser       1175             3
      1       5    650009 79915676       989 MDSOSS          npmuser       1175             3
我進行了2次查詢。第一次發現mdsdb2、mdsdb3使用者操作的插入物件正在被鎖定，並且鎖的級別都是6級，級別非常高，導致資源被佔用，如果此時在對錶操作就會報ora-00054，後來查詢了第二次，就沒有mdsdb2、mdsdb3使用者的鎖物件了，此時就可以操作表了。

4.查詢哪些使用者下哪些表被鎖住了
select A.sid, b.serial#,
  2  decode(A.type,
  3    'MR', 'Media Recovery',
  4    'RT','Redo Thread',
  5    'UN','User Name',
  6    'TX', 'Transaction',
  7    'TM', 'DML',
  8    'UL', 'PL/SQL User Lock',
  9    'DX', 'Distributed Xaction',
10    'CF', 'Control File',
11    'IS', 'Instance State',
12    'FS', 'File Set',
13    'IR', 'Instance Recovery',
14    'ST', 'Disk Space Transaction',
15    'TS', 'Temp Segment',
16    'IV', 'Library Cache Invalida-tion',
17    'LS', 'Log Start or Switch',
18    'RW', 'Row Wait',
19    'SQ', 'Sequence Number',
20    'TE', 'Extend Table',
21    'TT', 'Temp Table',
22    'Unknown') LockType,
23  c.object_name,
24  b.username,
25  b.osuser,
26  decode(a.lmode, 0, 'None',
27             1, 'Null',
28             2, 'Row-S',
29             3, 'Row-X',
30             4, 'Share',
31             5, 'S/Row-X',
32             6, 'Exclusive', 'Unknown') LockMode,
33  B.MACHINE,D.SPID
34  from v$lock a,v$session b,all_objects c,V$PROCESS D
35  where a.sid=b.sid and a.type in ('TM','TX')
36  and c.object_id=a.id1
37  AND B.PADDR=D.ADDR
38  ;

SID    SERIAL#    LOCKTYPE    OBJECT_NAME           USERNAME    OSUSER   LOCKMODE  MACHINE         SPID
--------------- ------------ ------------------- ----------------------- --------------- --------- ----------------------------- ------------

867      6254       DML               TMP2720003           MDSOSS       npmuser       RowX        TJGRAPP          28373
935    46865      DML              GNWEBBRW12082016 MDSOSS       mdsdb1    Exclusive    TJ-Unicom-Group-Analyse-02 4632
1120    33384    DML             TMP244701              MDSOSS       npmuser       RowX        TJGRAPP            844
1120    33384 DML            TMP244702              MDSOSS       npmuser       RowX        TJGRAPP            844
1120    33384 DML             TMP244703              MDSOSS       npmuser       Row-X       TJGRAPP            844
1030    53327 DML               GNMMO12082016     MDSOSS       mdsdb3         Exclusive TJ-Unicom-Group-Analyse-02 5211
867    6254    DML                TMP2720001            MDSOSS       npmuser       Row-X      TJGRAPP           28373
867    6254    DML                TMP2720002           MDSOSS       npmuser       Row-X      TJGRAPP           28373
1014     46058 DML               TMP748801              MDSOSS       npmuser       Row-X       TJGRAPP           3086
1014    46058 DML             TMP748802             MDSOSS       npmuser       Row-X       TJGRAPP           3086
1014    46058 DML               TMP748803              MDSOSS       npmuser       Row-X       TJGRAPP           3086
1176    22983 DML                TMP790301             MDSOSS       npmuser       Row-X       TJGRAPP           3168
994      20324 DML                  TMP791601             MDSOSS       npmuser        Row-X        TJGRAPP           3170

13 rows selected
小結：例如 GNWEBBRW12082016 這個表現在已經被mdsdb1使用者使用，在沒有commit之前，其他使用者是不能使用的，如果其他使用者此時也想訪問這個表就會發生ora-00054錯誤！就會有資源爭用導致資源忙等待
解決方法：
1.重啟資料庫 startup force
2.等待鎖釋放
3.強制kill會話

Leonarding
2012.8.20
天津&autumn
分享技術~收穫快樂
Blog：http://space.itpub.net/26686207

ora-00054 表被lock導致資源忙等待不能操作案例

相關文章