-- 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;
/