oracle 闪回事物查询,闪回版本查询与闪回事务查询

闪回版本查询： 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/，如需转载，请注明出处，否则将追究法律责任。