문서의 선택한 두 판 사이의 차이를 보여줍니다.
다음 판 | 이전 판 | ||
dbms:oracle:admin:flashback:transaction_backout [2016/05/08 06:18] starlits 만듦 |
dbms:oracle:admin:flashback:transaction_backout [2016/05/21 08:32] (현재) starlits |
||
---|---|---|---|
줄 6: | 줄 6: | ||
grant execute on dbms_flashback to hr; | grant execute on dbms_flashback to hr; | ||
grant select any transaction to hr; | grant select any transaction to hr; | ||
+ | </ | ||
+ | - 스크립트 실행 < | ||
+ | connect hr/oracle | ||
+ | select to_char(sysdate,' | ||
+ | |||
+ | INSERT INTO hr.regions VALUES (5,' | ||
+ | COMMIT; | ||
+ | |||
+ | UPDATE hr.regions SET region_name=' | ||
+ | UPDATE hr.regions SET region_name=' | ||
+ | COMMIT; | ||
+ | |||
+ | INSERT INTO hr.countries VALUES (' | ||
+ | COMMIT; | ||
+ | |||
+ | select to_char(sysdate,' | ||
+ | |||
+ | 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 --, | ||
+ | from flashback_transaction_query | ||
+ | where table_name IN (' | ||
+ | </ | ||
+ | - 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: | ||
+ | dbms_flashback.transaction_backout(1, | ||
+ | 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 --, | ||
+ | from flashback_transaction_query | ||
+ | where table_name IN (' | ||
</ | </ |