test20

最新推荐文章于 2021-11-19 20:51:32 发布

原创最新推荐文章于 2021-11-19 20:51:32 发布 · 118 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#SQL #Oracle

Oracle 专栏收录该内容

38 篇文章

订阅专栏

本文介绍如何使用Oracle数据库的闪回特性来恢复特定事务。通过查询版本信息和闪回事务查询表，可以找到并撤销不需要的更改。

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