adminschool.net wiki

이 문서는 읽기 전용입니다. 원본을 볼 수는 있지만 바꿀 수는 없습니다. 문제가 있다고 생각하면 관리자에게 문의하세요.

====== flashback transaction backout ======

  - 사전작업 <code>
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;
</code>
  - 스크립트 실행 <code>
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;
</code>
  - Query flashback_transaction_query to get the transaction id and the list of all transactional changes from the undo data <code>
select xid, table_name, operation, undo_sql --,start_timestamp,commit_timestamp 
from flashback_transaction_query 
where table_name IN ('COUNTRIES','REGIONS');
</code>
  - 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 <code>
declare
    v_txid xid_array;  
begin
    v_txid:=sys.xid_array('0800130091020000'); 
    dbms_flashback.transaction_backout(1,v_txid,dbms_flashback.cascade);
end;
</code>
  - Do not forget to commit <code>
commit;
</code>
  - 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 <code>
select xid, table_name, operation, undo_sql --,start_timestamp,commit_timestamp 
from flashback_transaction_query 
where table_name IN ('COUNTRIES','REGIONS');
</code>

adminschool.net wiki

사용자 도구

사이트 도구

문서 도구