同事查詢遇到如下錯誤：

SQL>select * from r_product_s_n t where t.update_date>trunc(sysdate)-0

ORA-01591:此一鎖定目前是由有問題的分散式交易12.45.132870所持有

ORA-01591: lock held by in-doubt distributed transaction string

Cause: Trying to access resource that is locked by a dead two-phase commit transaction that is in prepared state.

Action: DBA should query the pending_trans$ and related tables, and attempt to repair network connection(s) to coordinator and commit point. If timely repair is not possible, DBA should contact DBA at commit point if known or end user for correct outcome, or use heuristic default if given to issue a heuristic commit or abort command to finalize the local portion of the distributed transaction.

SQL>select * from sys.pending_trans$

OCAL_TRAN_ID GLOBAL_TRAN_FMT GLOBAL_ORACLE_ID GLOBAL_FOREIGN_ID TRAN_COMMENT STATE STATUS HEURISTIC_DFLT SESSION_VECTOR RECO_VECTOR TYPE# FAIL_TIME HEURISTIC_TIME RECO_TIME TOP_DB_USER TOP_OS_USER TOP_OS_HOST TOP_OS_TERMINAL GLOBAL_COMMIT# SPARE1 SPARE2 SPARE3 SPARE4
---------------------- --------------------------------------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------- ------ -------------- -------------- ----------- ---------- ----------- -------------- ----------- ------------------------------ ---------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------- ---------- ------------------------------ ---------- ------------------------------

1 12.45.132870 IPEG.REGRESS.RDBMS.DEV.US.ORACLE.COM.d8359c68.12.45.132870 prepared no 2011/11/6 ?? 02:12:14 2011/11/6 ?? 02:12:14 Administrator CC-CAAF66F6E6B2 WORKGROUP\CC-CAAF66F6E6B2 SFCC 11832246998835

解決過程：

1).commit force 12.45.132870,運行一小時沒有反應后中斷。

2).rollback force 12.45.132870,運行半小時沒有反應后中斷.

3).set transaction use rollback segment system;
　delete from dba_2pc_pending where local_tran_id = 12.45.132870 ;
　delete from pending_sessions$ where local_tran_id = 12.45.132870 ;
　delete from pending_sub_sessions$ where local_tran_id = 12.45.132870 ;
　commit;

4).以上方法沒有成功，只能重啟數據庫試試。

5).SQL> startup force;
ORACLE 執行處理已啟動

Total System Global Area 497995776 bytes
Fixed Size                  1337464 bytes
Variable Size             373294984 bytes
Database Buffers          117440512 bytes
Redo Buffers                5922816 bytes
資料庫已掛載

資料庫已開啟

SQL>select * from sys.pending_trans$

OCAL_TRAN_ID GLOBAL_TRAN_FMT GLOBAL_ORACLE_ID GLOBAL_FOREIGN_ID TRAN_COMMENT STATE STATUS HEURISTIC_DFLT SESSION_VECTOR RECO_VECTOR TYPE# FAIL_TIME HEURISTIC_TIME RECO_TIME TOP_DB_USER TOP_OS_USER TOP_OS_HOST TOP_OS_TERMINAL GLOBAL_COMMIT# SPARE1 SPARE2 SPARE3 SPARE4
---------------------- --------------------------------------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------- ------ -------------- -------------- ----------- ---------- ----------- -------------- ----------- ------------------------------ ---------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------- ---------- ------------------------------ ---------- ------------------------------

問題解決。

造成此類錯誤原因需深究。

補充：出現此問題首先應該使用

SQL>alter system set "_smu_debug_mode" = 4;

SQL>execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('12.40.55282');

如果不成功再考慮使用其他方法

ORA-01591報錯提示