【转】Oracle10g Flashback Query数据闪回

最新推荐文章于 2023-12-05 08:59:34 发布

原创最新推荐文章于 2023-12-05 08:59:34 发布 · 113 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#SQL

DataBase 专栏收录该内容

16 篇文章

订阅专栏

本文介绍Oracle10g中如何使用FlashbackVersionQuery功能进行事务级的数据恢复，包括执行DML操作及闪回版本查询的具体步骤，并展示了如何查看数据库中的历史变更。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Oracle10g通过Flashback Version Query提供查看对数据库事务级改变的方法.

当闪回事务处理查询与闪回版本查询同时使用时，我们可以轻易地从用户或者应用程序错误恢复。

以下是闪回版本查询的示例:

[b]1.执行DML操作[/b]


EYGLE  on 30-MAR-05 >create table t as select username,user_id from dba_users;



Table created.



EYGLE  on 30-MAR-05 >select * from t;



USERNAME                          USER_ID

------------------------------ ----------

SYSTEM                                  5

SYS                                     0

TEST                                   25

EYGLE                                  26

SCOTT                                  29

DIP                                    19

TRANS                                  27

TEST1                                  28

OPERATOR                               31

WMSYS                                  23

DBSNMP                                 22



USERNAME                          USER_ID

------------------------------ ----------

OUTLN                                  11



12 rows selected.



EYGLE  on 30-MAR-05 >delete from t where username='OUTLN';



1 row deleted.



EYGLE  on 30-MAR-05 >commit;



Commit complete.



EYGLE  on 30-MAR-05 >delete from t where username='TEST1';



1 row deleted.



EYGLE  on 30-MAR-05 >commit;



Commit complete.



EYGLE  on 30-MAR-05 >select * from t;



USERNAME                          USER_ID

------------------------------ ----------

SYSTEM                                  5

SYS                                     0

TEST                                   25

EYGLE                                  26

SCOTT                                  29

DIP                                    19

TRANS                                  27

OPERATOR                               31

WMSYS                                  23

DBSNMP                                 22



10 rows selected.



EYGLE  on 30-MAR-05 >update t set user_id=1 where username='EYGLE';



1 row updated.



EYGLE  on 30-MAR-05 >commit;



Commit complete.



EYGLE  on 30-MAR-05 >delete from t where user_id >10;



7 rows deleted.



EYGLE  on 30-MAR-05 >commit;



Commit complete.



EYGLE  on 30-MAR-05 >select * from t;



USERNAME                          USER_ID

------------------------------ ----------

SYSTEM                                  5

SYS                                     0

EYGLE                                   1



EYGLE  on 30-MAR-05 >insert into t values('PENNY',2);



1 row created.



EYGLE  on 30-MAR-05 >commit;



Commit complete.

[b]2.执行闪回版本查询[/b]

EYGLE  on 30-MAR-05 >select versions_starttime, versions_endtime, versions_xid,

  2          versions_operation, username,user_id

  3          from t versions between timestamp minvalue and maxvalue

  4  /



VERSIONS_STARTTIME             VERSIONS_ENDTIME               VERSIONS_XID     V USERNAME      USER_ID

------------------------------ ------------------------------ ---------------- - ---------- ----------

30-MAR-05 09.34.49 AM                                         000A000B000000F1 D DBSNMP             22

30-MAR-05 09.34.49 AM                                         000A000B000000F1 D WMSYS              23

30-MAR-05 09.34.49 AM                                         000A000B000000F1 D OPERATOR           31

30-MAR-05 09.34.49 AM                                         000A000B000000F1 D TRANS              27

30-MAR-05 09.34.49 AM                                         000A000B000000F1 D DIP                19

30-MAR-05 09.34.49 AM                                         000A000B000000F1 D SCOTT              29

30-MAR-05 09.34.49 AM                                         000A000B000000F1 D TEST               25

30-MAR-05 09.34.15 AM                                         0001001900000F0F U EYGLE               1

30-MAR-05 09.33.51 AM                                         00080016000000EF D TEST1              28

30-MAR-05 09.33.23 AM                                         0004000A000005EF D OUTLN              11

                                                                                 SYSTEM              5



VERSIONS_STARTTIME             VERSIONS_ENDTIME               VERSIONS_XID     V USERNAME      USER_ID

------------------------------ ------------------------------ ---------------- - ---------- ----------

                                                                                 SYS                 0

                               30-MAR-05 09.34.49 AM                             TEST               25

                               30-MAR-05 09.34.15 AM                             EYGLE              26

                               30-MAR-05 09.34.49 AM                             SCOTT              29

                               30-MAR-05 09.34.49 AM                             DIP                19

                               30-MAR-05 09.34.49 AM                             TRANS              27

                               30-MAR-05 09.33.51 AM                             TEST1              28

                               30-MAR-05 09.34.49 AM                             OPERATOR           31

                               30-MAR-05 09.34.49 AM                             WMSYS              23

                               30-MAR-05 09.34.49 AM                             DBSNMP             22

                               30-MAR-05 09.33.23 AM                             OUTLN              11



VERSIONS_STARTTIME             VERSIONS_ENDTIME               VERSIONS_XID     V USERNAME      USER_ID

------------------------------ ------------------------------ ---------------- - ---------- ----------

30-MAR-05 09.49.24 AM                                         00080006000000EF I PENNY               2



23 rows selected.



EYGLE  on 30-MAR-05 >