舉例說明ORA-01591錯誤處理

錯誤信息:
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;