ORA-01591報錯提示

最新推荐文章于 2025-06-20 21:00:00 发布

csdnww1209

最新推荐文章于 2025-06-20 21:00:00 发布

阅读量110

点赞数

本文详细介绍了在Oracle数据库中遇到分布式事务锁定错误时的排查与解决过程，包括使用特定命令清理遗留事务、强制提交或回滚有问题的事务、删除相关事务记录以及最终重启数据库等步骤。通过这些操作，成功解决了锁定问题并恢复了数据库的正常运行。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

同事查詢遇到如下錯誤：

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$

問題解決。

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

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

SQL>alter system set "_smu_debug_mode" = 4;

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

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16381228/viewspace-710376/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16381228/viewspace-710376/