test20

如何只恢复经过多次commit后的某个事务

SQL>  insert into t values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> commit;
Commit complete.
SQL> delete from t where id = 1;
1 row deleted.
SQL> commit;
Commit complete.

用versions子句，而且用versions_starttime等虚列可以找到其中每个事务的开始时间(versions_starttime)和
操作(versions_operation虚列，D-delete I-insert，U-update等)
SQL> select versions_starttime,versions_xid,versions_operation from t
  2  versions between timestamp minvalue and maxvalue;

VERSIONS_STARTTIME             VERSIONS_XID    V
----------------------------- ---------------- -
06-AUG-08 09.31.36 AM         17001C0079000000 D
06-AUG-08 09.31.27 AM         17001B0079000000 I
06-AUG-08 09.31.18 AM         17001A0079000000 I
然后从flashback_transaction_query表中根据相应的事务ID（versions_xid）可以找到相应的undo操作，
根据该操作可以直接rollback该事务！让人不得不佩服ORACLE的强大！
SQL> desc flashback_transaction_query;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 XID                                                RAW(8)
 START_SCN                                          NUMBER
....
 UNDO_SQL                                           VARCHAR2(4000)

SQL> select undo_sql from flashback_transaction_query where xid='17001A0079000000'
UNDO_SQL
--------------------------------------------------------------------------------
delete from "SYS"."T" where ROWID = 'AAAMSwAABAAANcaAAA';

SQL> select undo_sql from flashback_transaction_query where xid='17001C0079000000';
UNDO_SQL
--------------------------------------------------------------------------------
insert into "SYS"."T"("ID") values ('1');