DanBrother的部落格

-- Connect as sys

select count(*) from dba_2pc_pending;
select count(*) from dba_2pc_neighbors;

set serveroutput on size 1000000
declare
type script_table_type is table of varchar2(1000)
index by binary_integer;
script script_table_type;
v_line varchar2(200):='*************************************************************';
begin

for rec in (select local_tran_id,state,mixed,advice from DBA_2PC_PENDING) loop
   if rec.mixed = 'YES' then
      script(1):=v_line;
      script(2):='execute DBMS_TRANSACTION.PURGE_MIXED('''||rec.local_tran_id||''');';
      script(3):=v_line;
   elsif upper(rec.state) in ('COLLECTING','PREPARED') then     
      script(1):=v_line;
      script(2):='Mixed='||rec.mixed||' / Advice='||rec.advice;
      script(3):='rollback force '''||rec.local_tran_id||''';';
      script(4):='execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('''||rec.local_tran_id||''');';
      script(5):=v_line;
   end if;
   for i in script.FIRST .. script.LAST loop
       dbms_output.put_line(script(i));
   end loop;
end loop;
end;
/