如何快速、及時、準確的閃回DML提交後的資料

生產環境中，由於表的資料量非常大，我們或許只錯誤的刪除了其中一條資料。如果我們用flashback query+flashback table是非常得不償失的，不光有可能丟失一部分後來的資料，而且也不一定能閃回成功。在這種細粒度的閃回上，我們更應該使用flashback version+flashback transaction的方式來實現。由於儲存在undo中資料的時間受很多因素，當出現問題的時候，我們應該儘量讓undo儲存的資料時間更長些，已備我們有flashback的充足時間。

[oracle@rhel2 ~]$ sqlplus system/oracle

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Feb 7 00:09:03 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> create table test as select * from dba_extents;

Table created.

SQL> select count(*) from test;

  COUNT(*)
----------
      7134

SQL> delete from test where rowid='AAANBbAABAAAPOiAAG';

1 row deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from test;

  COUNT(*)
----------
      7133

SQL> alter tablespace UNDOTBS1 retention GUARANTEE;//確保自提交後undo_retention時間內undo裡的資料不會被清除

Tablespace altered.

SQL> alter tablespace UNDOTBS2 retention GUARANTEE;

Tablespace altered.

SQL> show parameters undo_tablespace

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS2
SQL> show parameter retention

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440
undo_retention                       integer     0
SQL> alter system set undo_retention=9000;//增大undo_retention的值，讓在Undo裡的資料儲存更長時間

System altered.

SQL> create undo tablespace undotmp01 datafile '+DG1' size 10m autoextend on next 10m;

Tablespace created.

SQL> create undo tablespace undotmp02 datafile '+DG1' size 10m autoextend on next 10m;

Tablespace created.

SQL> alter system set undo_tablespace='UNDOTMP01' scope=memory sid='ractest2';//將建立好的臨時undo tablespace設定為當前Undo，這也是為了確保以前的undo資料能夠儲存更長時間，而不會被覆蓋。資料恢復完成後再切換回原始的undo表空間，當然臨時undo也不必刪除，可供以後出現類似情況更快速的完成切換。

System altered.

SQL> alter system set undo_tablespace='UNDOTMP02' scope=memory sid='ractest1';

System altered.

SQL> set linesize 200;
SQL> col versions_operation format a10;
SQL> col versions_xid format a20;

SQL> select versions_operation,
  2         versions_xid,
  3         rowid
  4    from test versions between timestamp minvalue and maxvalue
  5   where versions_operation = 'D';                            //flashback version

VERSIONS_O VERSIONS_XID         ROWID
---------- -------------------- ------------------
D          0E001F0058000000     AAANBbAABAAAPOiAAG

SQL> select undo_sql
  2    from flashback_transaction_query
  3   where peration = 'DELETE'
  4     and xid = hextoraw('0E001F0058000000');                  //flashback transaction

UNDO_SQL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
insert into "SYSTEM"."TEST"("OWNER","SEGMENT_NAME","PARTITION_NAME","SEGMENT_TYPE","TABLESPACE_NAME","EXTENT_ID","FILE_ID","BLOCK_ID","BYTES","BLOCKS","RELATIVE_FNO") values ('SYS','SYS_LOB0000037868C
00005$$',NULL,'LOBSEGMENT','SYSTEM','0','1','44249','65536','8','1');

SQL> insert into "SYSTEM"."TEST"("OWNER","SEGMENT_NAME","PARTITION_NAME","SEGMENT_TYPE","TABLESPACE_NAME","EXTENT_ID","FILE_ID","BLOCK_ID","BYTES","BLOCKS","RELATIVE_FNO") values ('SYS','SYS_LOB0000037868C00005$$',NULL,'LOBSEGMENT','SYSTEM','0','1','44249','65536','8','1');

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from test;

  COUNT(*)
----------
      7134

SQL>

至此，由使用者操作錯誤刪除的一條資料就還原成功了，之後操作的資料也沒有任何丟失。