闪回版本查询与闪回事务查询

闪回版本查询： Flashback Versions Query
    闪回版本查询允许使用一个version子句查询两个时间点或者SCN之间的数据版本。这些版本可以按照事务进行区分，闪回版本查询只返回提交数据，未提交数据不被显示。
   在scott模式下运行以下脚本。
  create table myemp as select * from emp;


  begin
    for rec in 1..10 loop
      update myemp set sal = sal + rec where empno = 7369;
      commit;
    end loop;
  end;
 
 

 从查询中可以看出，闪回版本查询可以获取各个发生修改过的数据以及对应事务的事务ID，操作类型，版本的时间戳的信息。
闪回事务查询： Flashback Transaction Query
     有了闪回版本查询的基础，就可以进行基于flashback version query的事务级恢复。
     闪回事务查询相关视图的脚步。 
     create or replace view flashback_transaction_query as
select xid, start_scn, start_timestamp,
          decode(commit_scn, 0, commit_scn, 281474976710655, NULL, commit_scn)
          commit_scn, commit_timestamp,
          logon_user, undo_change#, operation, table_name, table_owner,
          row_id, undo_sql
from sys.x$ktuqqry;
comment on table FLASHBACK_TRANSACTION_QUERY is 'Description of the flashback transaction query view';
comment on column FLASHBACK_TRANSACTION_QUERY.XID is 'Transaction identifier';
comment on column FLASHBACK_TRANSACTION_QUERY.START_SCN is 'Transaction start SCN';
comment on column FLASHBACK_TRANSACTION_QUERY.START_TIMESTAMP is 'Transaction start timestamp';
comment on column FLASHBACK_TRANSACTION_QUERY.COMMIT_SCN is 'Transaction commit SCN';
comment on column FLASHBACK_TRANSACTION_QUERY.COMMIT_TIMESTAMP is 'Transaction commit timestamp';
comment on column FLASHBACK_TRANSACTION_QUERY.LOGON_USER is 'Logon user for transaction';
comment on column FLASHBACK_TRANSACTION_QUERY.UNDO_CHANGE# is '1-based undo change number';
comment on column FLASHBACK_TRANSACTION_QUERY.OPERATION is 'forward operation for this undo';
comment on column FLASHBACK_TRANSACTION_QUERY.TABLE_NAME is 'table name to which this undo applies';
comment on column FLASHBACK_TRANSACTION_QUERY.TABLE_OWNER is 'owner of table to which this undo applies';
comment on column FLASHBACK_TRANSACTION_QUERY.ROW_ID is 'rowid to which this undo applies';
comment on column FLASHBACK_TRANSACTION_QUERY.UNDO_SQL is 'SQL corresponding to this undo';

如果要获取UNDO_SQL需要开启附加日志：
alter database add supplemental log data;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8520577/viewspace-1255460/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8520577/viewspace-1255460/