ora-02049解決一例

執行儲存過程更新一個表中的資料的時候產生如下的錯誤：

SQL> exec update_jc_kxx_yxrq;

begin update_jc_kxx_yxrq; end;

ORA-20998: Err=-2049,Msg=0-ORA-02049: 超時: 分散式事務處理等待鎖定
ORA-06512: 在"ICUSER.UPDATE_JC_KXX_YXRQ", line 36
ORA-06512: 在line 2

[@more@]

以sys使用者登陸資料庫查詢死鎖

SQL> select username,lockwait,status,machine,program from v$session where sid in
2 (select session_id from v$locked_object);

USERNAME LOCKWAIT STATUS
------------------------------ ---------------- --------
MACHINE
----------------------------------------------------------------
PROGRAM
------------------------------------------------
ICUSER 000000038A37B7A8 ACTIVE
icdb
JDBC Thin Client

ICUSER 000000038A37C040 ACTIVE
icdb
JDBC Thin Client

USERNAME LOCKWAIT STATUS
------------------------------ ---------------- --------
MACHINE
----------------------------------------------------------------
PROGRAM
------------------------------------------------

ICUSER 000000038A37BF30 ACTIVE
icdb
JDBC Thin Client

ICUSER INACTIVE
icdb

USERNAME LOCKWAIT STATUS
------------------------------ ---------------- --------
MACHINE
----------------------------------------------------------------
PROGRAM
------------------------------------------------
JDBC Thin Client

ICUSER 000000038A37C150 ACTIVE
WorkGroupNameIC
collect.exe

ICUSER 000000038A37BFB8 ACTIVE

USERNAME LOCKWAIT STATUS
------------------------------ ---------------- --------
MACHINE
----------------------------------------------------------------
PROGRAM
------------------------------------------------
icdb
JDBC Thin Client

ICUSER 000000038A37C0C8 ACTIVE
icdb
JDBC Thin Client

說明資料庫有死鎖

然後使用一下語句查詢被死鎖的語句

SQL> select sql_text from v$sql where hash_value in
2 (select sql_hash_value from v$session where sid in
3 (select session_id from v$locked_object));

SQL_TEXT
--------------------------------------------------------------------------------
update JC_KXX SET LJXF =NVL ( LJXF , 0 ) + :1 , YE =:2 WHERE KH =:3
update jc_kxx set zt='07' where kh='1000530330'

再使用以下語句查詢被死鎖的程式

SQL> SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,
2 l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS
3 FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;

USERNAME OBJECT_ID SESSION_ID SERIAL#
------------------------------ ---------- ---------- ----------
ORACLE_USERNAME OS_USER_NAME PROCESS
------------------------------ ------------------------------ ------------
ICUSER 30523 32 42463
ICUSER oracle

ICUSER 30523 28 25508
ICUSER oracle

ICUSER 30523 76 14781
ICUSER oracle


USERNAME OBJECT_ID SESSION_ID SERIAL#
------------------------------ ---------- ---------- ----------
ORACLE_USERNAME OS_USER_NAME PROCESS
------------------------------ ------------------------------ ------------
ICUSER 30523 88 3943
ICUSER oracle

ICUSER 30523 46 17889
ICUSER oracle

ICUSER 30582 46 17889
ICUSER oracle


USERNAME OBJECT_ID SESSION_ID SERIAL#
------------------------------ ---------- ---------- ----------
ORACLE_USERNAME OS_USER_NAME PROCESS
------------------------------ ------------------------------ ------------
ICUSER 30603 46 17889
ICUSER oracle

ICUSER 30592 46 17889
ICUSER oracle

ICUSER 30588 46 17889
ICUSER oracle


USERNAME OBJECT_ID SESSION_ID SERIAL#
------------------------------ ---------- ---------- ----------
ORACLE_USERNAME OS_USER_NAME PROCESS
------------------------------ ------------------------------ ------------
ICUSER 30659 62 34578
ICUSER Administrator 1056:1020

ICUSER 30523 62 34578
ICUSER Administrator 1056:1020

ICUSER 30669 62 34578
ICUSER Administrator 1056:1020


USERNAME OBJECT_ID SESSION_ID SERIAL#
------------------------------ ---------- ---------- ----------
ORACLE_USERNAME OS_USER_NAME PROCESS
------------------------------ ------------------------------ ------------
ICUSER 30523 24 37522
ICUSER oracle

使用一下語句把死鎖的程式kill

alter system kill session ‘sid,serial#’; （其中sid=l.session_id）

如：SQL> alter system kill session '24,37522';

再次執行儲存過程，錯誤沒有了。語句執行成功！