如何只恢复经过多次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');