錯誤信息:
ORA-01591:此一鎖定目前是由有問題的分散式交易 7.32.72905 所持有.
發生原因:
由於遠程資料庫的異常中斷或者是關閉,本地資料庫對遠程資料庫的分散式交易會出錯.
解決方法:
方法一:
alter session set "_smu_debug_mode" = 4;
execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('7.32.72905');
方法二:
--1.查询資料字典,以找到被鎖定的交易ID:
SELECT * FROM DBA_2PC_PENDING WHERE STATE='collecting';
--2.將其COMMIT或ROLLBACK:
rollback force '7.32.72905';
commit force '7.32.72905';
--3.如果執行失敗,則強行從資料字典中刪除該交易記錄:
SQL> set transaction use rollback segment system;
SQL> delete from dba_2pc_pending where local_tran_id = "7.32.72905";
SQL> delete from pending_sessions$ where local_tran_id = "7.32.72905";
SQL> delete from pending_sub_sessions$ where local_tran_id = "7.32.72905";
SQL> commit;
begin
for r in (select local_tran_id
from dba_2pc_pending t
where t.state = 'collecting') loop
--dbms_output.put_line('commit force '''||r.local_tran_id||''';');
dbms_output.put_line('delete from dba_2pc_pending where local_tran_id = '''||r.local_tran_id||''';');
dbms_output.put_line('delete from pending_sessions$ where local_tran_id = '''||r.local_tran_id||''';');
dbms_output.put_line('delete from pending_sub_sessions$ where local_tran_id = '''||r.local_tran_id||''';');
end loop;
dbms_output.put_line('commit;');
end;