Oracle10g的Flashback version Query

« 使用Oracle10g的Flashback Query进行数据闪回 | Blog首页 | 通过Oracle10g的flashback transaction query新特性进行事务撤销 »

Oracle10g通过Flashback Version Query提供查看对数据库事务级改变的方法.
当闪回事务处理查询与闪回版本查询同时使用时，我们可以轻易地从用户或者应用程序错误恢复。
以下是闪回版本查询的示例:
1.执行DML操作



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.

2.执行闪回版本查询



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 >

我们可以看到，以上事务的时间以及数据更改。

历史上的今天...
>> 2019-03-30文章:

2015 数据库Gartner市场份额-Oracle 45.6% 占据领先优势

>> 2017-03-30文章:

Oracle 11g 密码延迟认证与 library cache lock 等待

>> 2012-03-30文章:

Oracle Security Alert for CVE-2011-5035 OC4J

数据恢复:ORA-600 kccpb_sanity_check_2解决

>> 2011-03-30文章:

恩墨科技2011性能优化培训成功结束

>> 2009-03-30文章:

关于ocssd进程的三言两语

>> 2008-03-30文章: