閃回版本查詢與閃回事務查詢

閃回版本查詢： 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/，如需轉載，請註明出處，否則將追究法律責任。