Oracle10g闪回查询特性的增强

Oracle9i提供的闪回特性增强，为数据恢复带来了极大的方便，但是Oracle9i的闪回查询只能提供某个时间点的数据视图，并不能告诉我们这样的数据经过了几个事务、怎样的修改（update,insert,delete等），而这些信息在回滚段中是存在的，在Oracle10g中，Oracle进一步加强了闪回查询的特性，提供了以下两种闪回查询：

闪回版本查询- Flashback Versions Query

闪回事务查询- Flashback Transaction Query

闪回版本查询允许使用一个新的VERSIONS子句查询两个时间点或者SCN之间的数据版本。这些版本可以按照事务进行区分，闪回版本查询只返回提交数据，未提交数据不被显示。

通过以下示例，来理解闪回版本查询的作用。首先创建一个测试表，执行一系列的DML操作：

SQL>create table t as select username,user_id from dba_users;
Table created.
SQL>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
OUTLN                                  11
 
12 rows selected.
SQL>delete from t where username='OUTLN';
1 row deleted.
SQL>commit;
Commit complete.
SQL>delete from t where username='TEST1';
1 row deleted.
SQL>commit;
Commit complete.
再执行一系列DML操作并提交：
SQL>update t set user_id=1 where username='EYGLE';
1 row updated.
SQL>commit;
Commit complete.
SQL>delete from t where user_id >10;
7 rows deleted.
SQL>commit;
Commit complete.
SQL>select * from t;
USERNAME                          USER_ID
------------------------------ ----------
SYSTEM                                  5
SYS                                      0
EYGLE                                   1
SQL>insert into t values('PENNY',2);
1 row created.
SQL>commit;
Commit complete.

至此数据库中已经交替执行了多个事务，进行了众多的数据修改，现在的测试表已经与最初完全不同了。如果使用Oracle9i的闪回查询，是很难区分这些不同事务的变更，找到合适的、正确的数据将变得极为困难。

再来看看Oracle10g的闪回版本查询，通过使用versions子句和对数据表引入了一系列的伪列（version_starttime等），我们可以获得对数据表的所有事务操作，注意以下输出中versions_operation代表了不同类型的操作（D-Delete,I-Insert,U-Update）, VERSIONS_XID是一个重要数据，代表了不同版本的事务ID：

SQL>select versions_starttime, versions_endtime, versions_xid,
  2          versions_operation, username,user_id
  3          from t versions between timestamp minvalue and maxvalue;
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
                                                                 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
30-MAR-05 09.49.24 AM                         00080006000000EF I PENNY           2
23 rows selected.

通过以上输出，我们根据VERSIONS_XID可以清晰地区分不同事务在不同时间对数据所作的更改。具备了flashback version query查询的基础，就可以进行基于flashback version query的事务级恢复，这就是flashback transaction query。

flashback transaction query可以从FLASHBACK_TRANSACTION_QUERY视图中获得指定事务的历史信息以及Undo_SQL，通过这个UNDO_SQL，我们就可以撤销特定的提交事务。

Flashback transaction query需要用到FLASHBACK_TRANSACTION_QUERY视图，我们先看一下视图

SQL> desc FLASHBACK_TRANSACTION_QUERY;
Name             Type           Nullable Default Comments                
---------------- -------------- -------- ------- --------------------------
XID              RAW(8)          Y       Transaction identifier   
START_SCN        NUMBER          Y       Transaction start SCN    
START_TIMESTAMP  DATE            Y       Transaction start timestamp   
COMMIT_SCN       NUMBER          Y       Transaction commit SCN  
COMMIT_TIMESTAMP DATE            Y       Transaction commit timestamp  
LOGON_USER       VARCHAR2(30)    Y       Logon user for transaction   
UNDO_CHANGE#     NUMBER          Y       1-based undo change number  
OPERATION        VARCHAR2(32)    Y       forward operation for this undo  
TABLE_NAME       VARCHAR2(256)   Y       table name to which this undo applies     
TABLE_OWNER      VARCHAR2(32)    Y       owner of table to which this undo applies
ROW_ID           VARCHAR2(19)    Y       rowid to which this undo applies          
UNDO_SQL         VARCHAR2(4000)  Y       SQL corresponding to this undo

该视图的定义为：

select xid, start_scn, start_timestamp,
          decode(commit_scn, 0, commit_scn, 281474976710655, NULL, commit_scn)
          commit_scn, commit_timestamp,
          logon_user, undo_change#, operation, table_name, table_owner,
          row_id, undo_sql
from sys.x$ktuqqry

继续前面的测试，如果需要撤销XID=000A000B000000F1的事务，可以通过如下步骤进行（注意当查询FLASHBACK_TRANSACTION_QUERY视图时如果直接引用XID则查询会因为无法使用索引而极其耗时）：

SQL>set autotrace on
SQL>SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY
  2  WHERE XID = '000A000B000000F1';
UNDO_SQL
--------------------------------------------------------------------------------
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('DBSNMP','22');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('WMSYS','23');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('OPERATOR','31');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('TRANS','27');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('DIP','19');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('SCOTT','29');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('TEST','25');
8 rows selected.
Elapsed: 00:05:55.30
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=25 Card=1 Bytes=2008)
   1    0   FIXED TABLE (FULL) OF 'X$KTUQQRY' (TABLE (FIXED)) (Cost=25 Card=1 Bytes=2008)
 
Statistics
----------------------------------------------------------
     393454  recursive calls
          0  db block gets
    1562425  consistent gets
       4644  physical reads
          0  redo size
       1069  bytes sent via SQL*Net to client
        664  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
      23166  sorts (memory)
          0  sorts (disk)
          8  rows processed

此时可以通过hextoraw转换利用底层索引，提高查询速度：

SQL> set autotrace trace explain
SQL> SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY
  2  WHERE XID = hextoraw('09000D00A3080000');
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1747778896
---------------------------------------------------------------------------------------
| Id  | Operation               | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                   |     1 |  2008 |     0   (0)| 00:00:01 |
|*  1 |  FIXED TABLE FIXED INDEX| X$KTUQQRY (ind:1) |     1 |  2008 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("XID"=HEXTORAW('09000D00A3080000') )

通过执行相应的UNDO语句我们可以撤销该事务，通过这些新特性，Oracle为我们提供了一种“回滚”提交事务的手段，极大的方便了我们应对不同情况的数据库恢复。