문서의 이전 판입니다!

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');