Oracle 10g 闪回查询特性的增强-根据版本和事务恢复

1.创建测试表
SQL> create table tb_test as select username,user_id from dba_users;

Table created.

2.查看表数据
SQL> select * from tb_test;

USERNAME                          USER_ID
------------------------------ ----------
SYS                                     0
SYSTEM                                  5
TEST01                                 56
CHENLI                                 60
KETTLE                                 58
KETTLENEW                              59
TEST02                                 57
HXL                                    55
OUTLN                                  11
MGMT_VIEW                              53
MDSYS                                  46

USERNAME                          USER_ID
------------------------------ ----------
ORDSYS                                 43
EXFSYS                                 34
DMSYS                                  35
DBSNMP                                 24
WMSYS                                  25
CTXSYS                                 36
ANONYMOUS                              39
SYSMAN                                 51
XDB                                    38
ORDPLUGINS                             44
SI_INFORMTN_SCHEMA                     45

USERNAME                          USER_ID
------------------------------ ----------
OLAPSYS                                47
SCOTT                                  54
TSMSYS                                 21
MDDATA                                 50
DIP                                    19

27 rows selected.

3.对测试表进行相应操作
SQL> delete from tb_test where username='TEST01';

1 row deleted.

SQL> commit;

Commit complete.

SQL> delete from tb_test where username='KETTLE';

1 row deleted.

SQL> commit;

Commit complete.

SQL> update tb_test set user_id = 6 where username = 'CHENLI';

1 row updated.

SQL> commit;

Commit complete.
SQL> delete from tb_test where user_id > 10;

22 rows deleted.

SQL> commit;

Commit complete.

SQL> insert into tb_test values('TEST',8);

1 row created.

SQL> commit;

Commit complete.

4.
SQL> select
  2         versions_xid,
  3         versions_operation,
  4         username,
  5         user_id
  6    from tb_test versions between timestamp minvalue and maxva

VERSIONS_XID     V USERNAME                          USER_ID
---------------- - ------------------------------ ----------
06002B0034020000 D DIP                                    19
06002B0034020000 D MDDATA                                 50
06002B0034020000 D TSMSYS                                 21
06002B0034020000 D SCOTT                                  54
06002B0034020000 D OLAPSYS                                47
06002B0034020000 D SI_INFORMTN_SCHEMA                     45
06002B0034020000 D ORDPLUGINS                             44
06002B0034020000 D XDB                                    38
06002B0034020000 D SYSMAN                                 51
06002B0034020000 D ANONYMOUS                              39
06002B0034020000 D CTXSYS                                 36

VERSIONS_XID     V USERNAME                          USER_ID
---------------- - ------------------------------ ----------
06002B0034020000 D WMSYS                                  25
06002B0034020000 D DBSNMP                                 24
06002B0034020000 D DMSYS                                  35
06002B0034020000 D EXFSYS                                 34
06002B0034020000 D ORDSYS                                 43
06002B0034020000 D MDSYS                                  46
06002B0034020000 D MGMT_VIEW                              53
06002B0034020000 D OUTLN                                  11
06002B0034020000 D HXL                                    55
06002B0034020000 D TEST02                                 57
06002B0034020000 D KETTLENEW                              59

VERSIONS_XID     V USERNAME                          USER_ID
---------------- - ------------------------------ ----------
0300270031020000 U CHENLI                                  6
05000C0039020000 D KETTLE                                 58
090027004A020000 D TEST01                                 56
                   SYS                                     0
                   SYSTEM                                  5
                   TEST01                                 56
                   CHENLI                                 60
                   KETTLE                                 58
                   KETTLENEW                              59
                   TEST02                                 57
                   HXL                                    55

VERSIONS_XID     V USERNAME                          USER_ID
---------------- - ------------------------------ ----------
                   OUTLN                                  11
                   MGMT_VIEW                              53
                   MDSYS                                  46
                   ORDSYS                                 43
                   EXFSYS                                 34
                   DMSYS                                  35
                   DBSNMP                                 24
                   WMSYS                                  25
                   CTXSYS                                 36
                   ANONYMOUS                              39
                   SYSMAN                                 51

VERSIONS_XID     V USERNAME                          USER_ID
---------------- - ------------------------------ ----------
                   XDB                                    38
                   ORDPLUGINS                             44
                   SI_INFORMTN_SCHEMA                     45
                   OLAPSYS                                47
                   SCOTT                                  54
                   TSMSYS                                 21
                   MDDATA                                 50
                   DIP                                    19
01000B00F5010000 I TEST                                    8

53 rows selected.

5.找到某个XID对应的undo_sql
select xid, undo_sql
  from flashback_transaction_query t
 where xid = hextoraw('06002B0034020000');

06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('DIP','19');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('MDDATA','50');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('TSMSYS','21');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('SCOTT','54');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('OLAPSYS','47');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('SI_INFORMTN_SCHEMA','45');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('ORDPLUGINS','44');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('XDB','38');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('SYSMAN','51');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('ANONYMOUS','39');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('CTXSYS','36');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('WMSYS','25');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('DBSNMP','24');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('DMSYS','35');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('EXFSYS','34');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('ORDSYS','43');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('MDSYS','46');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('MGMT_VIEW','53');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('OUTLN','11');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('HXL','55');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('TEST02','57');
06002B0034020000 insert into "HXL"."TB_TEST"("USERNAME","USER_ID") values ('KETTLENEW','59');
06002B0034020000

执行以上语句即可闪回xid=06002B0034020000的事物.

-- The End --