5.使用Flashback version Query
闪回版本查询的测试：
- SQL> create table t as select username,user_id from dba_users;
Table created.
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
USERNAME USER_ID
------------------------------ ----------
MGMT_VIEW 53
SYS 0
SYSTEM 5
DBSNMP 24
SYSMAN 51
SJH 55
OUTLN 11
MDSYS 46
ORDSYS 43
CTXSYS 36
ANONYMOUS 39
USERNAME USER_ID
------------------------------ ----------
EXFSYS 34
DMSYS 35
WMSYS 25
XDB 38
ORDPLUGINS 44
SI_INFORMTN_SCHEMA 45
OLAPSYS 47
MDDATA 50
DIP 19
SCOTT 54
TSMSYS 21
22 rows selected.
--以下执行一些DML语句：
- SQL> delete from t where username='SYS';
1 row deleted.
Commit complete.
- SQL> DELETE FROM t where username='SYSTEM';
1 row deleted.
Commit complete.
- SQL> UPDATE t set user_id=0 where username='SJH';
1 row updated.
Commit complete.
- SQL> update t set user_id=1 where username='SCOTT';
1 row updated.
Commit complete.
USERNAME USER_ID
------------------------------ ----------
MGMT_VIEW 53
DBSNMP 24
SYSMAN 51
SJH 0
OUTLN 11
MDSYS 46
ORDSYS 43
CTXSYS 36
ANONYMOUS 39
EXFSYS 34
DMSYS 35
USERNAME USER_ID
------------------------------ ----------
WMSYS 25
XDB 38
ORDPLUGINS 44
SI_INFORMTN_SCHEMA 45
OLAPSYS 47
MDDATA 50
DIP 19
SCOTT 1
TSMSYS 21
20 rows selected.
--执行Flashback version Query
- SQL> select versions_starttime, versions_endtime, versions_xid,versions_operation, username,user_id from t versions between timestamp minvalue and maxvalue;
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID VERSIONS_OPERATION USERNAME USER_ID
------------------------------------------------- ------------------------------------------------- ---------------- ------------------ ------------------------------ ----------
26-MAR-09 01.58.03 PM 02001B0010030000 U SCOTT 1
26-MAR-09 01.57.21 PM 01002600CF020000 U SJH 0
26-MAR-09 01.56.12 PM 07000F00C5020000 D SYSTEM 5
26-MAR-09 01.55.48 PM 0A001700C0020000 D SYS 0
MGMT_VIEW 53
26-MAR-09 01.55.48 PM SYS 0
26-MAR-09 01.56.12 PM
--为了不显得格式凌乱，后面的记录省略。。。
--下面我们利用VERSIONS_XID来查询undo语句，需要用到sys/system用户。最后利用这些undo来撤销不同版本的事物。
- SQL> SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY where XID='0A001700C0020000';
UNDO_SQL
--------------------------------------------------------------------------------
insert into "SJH"."t"("USERNAME","USER_ID") values ('SYS','0');
- SQL> SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY where XID='07000F00C5020000';
UNDO_SQL
--------------------------------------------------------------------------------
insert into "SJH"."t"("USERNAME","USER_ID") values ('SYSTEM','5');
- SQL> SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY where XID='01002600CF020000';
UNDO_SQL
--------------------------------------------------------------------------------
update "SJH"."t" set "USER_ID" = '55' where ROWID = 'AAAM3JAAEAAAABMAAF';
- SQL> SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY where XID='02001B0010030000';
UNDO_SQL
--------------------------------------------------------------------------------
update "SJH"."SJH0" set "USER_ID" = '54' where ROWID = 'AAAM3JAAEAAAABMAAU';
- SQL> insert into "SJH"."t"("USERNAME","USER_ID") values ('SYS','0');
1 row created.
- SQL> insert into "SJH"."t"("USERNAME","USER_ID") values ('SYSTEM','5');
1 row created.
- SQL> update "SJH"."t" set "USER_ID" = '55' where ROWID = 'AAAM3JAAEAAAABMAAF';
1 row updated.
- SQL> update "SJH"."t" set "USER_ID" = '54' where ROWID = 'AAAM3JAAEAAAABMAAU';
1 row updated.
Commit complete.
USERNAME USER_ID
------------------------------ ----------
MGMT_VIEW 53
DBSNMP 24
SYSMAN 51
SJH 55
OUTLN 11
MDSYS 46
ORDSYS 43
CTXSYS 36
ANONYMOUS 39
EXFSYS 34
DMSYS 35
USERNAME USER_ID
------------------------------ ----------
WMSYS 25
XDB 38
ORDPLUGINS 44
SI_INFORMTN_SCHEMA 45
OLAPSYS 47
MDDATA 50
DIP 19
SCOTT 54
TSMSYS 21
SYS 0
SYSTEM 5
22 rows selected.
--完成事务的撤销。
--Flashback transaction query需要用到FLASHBACK_TRANSACTION_QUERY视图。这个视图来源于x$ktuqqry表且xid字段上不存在索引，所以走的是全表扫描。x$ktuqqry的查询非常耗时，所以请注意评估你的恢复成本。
