flashback transaction backout

사전작업

alter database add supplemental log data;
alter database add supplemental log data (primary key) columns; 
grant execute on dbms_flashback to hr;
grant select any transaction to hr;

스크립트 실행

connect hr/oracle
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;

INSERT INTO hr.regions VALUES (5,'Pole'); 
COMMIT;

UPDATE hr.regions SET region_name='Poles' WHERE region_id = 5;
UPDATE hr.regions SET region_name='North and South Poles' WHERE region_id = 5;
COMMIT;

INSERT INTO hr.countries VALUES ('TT','Test Country',5); 
COMMIT;

select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;

connect / as sysdba
ALTER SYSTEM ARCHIVE LOG CURRENT;

Query flashback_transaction_query to get the transaction id and the list of all transactional changes from the undo data

select xid, table_name, operation, undo_sql --,start_timestamp,commit_timestamp 
from flashback_transaction_query 
where table_name IN ('COUNTRIES','REGIONS');

Now use the transaction_backout procedure with the cascade option to get back all dependent transactions to keep the data consistency. You can pass the list of all transactions to the variable which was declared with default data type: xid_array. In this example, use only one transaction

declare
    v_txid xid_array;  
begin
    v_txid:=sys.xid_array('0800130091020000'); 
    dbms_flashback.transaction_backout(1,v_txid,dbms_flashback.cascade);
end;

Do not forget to commit

commit;

Now query the flashback_transaction_query view to see which transactions run to roll back the changes. Do not forget to query the two tables

select xid, table_name, operation, undo_sql --,start_timestamp,commit_timestamp 
from flashback_transaction_query 
where table_name IN ('COUNTRIES','REGIONS');