基于时间的闪回

如果用户delete了表部分数据后且进行commit操作后发现问题，则处理相过程具体见下：
--查看测试表及数据
SQL> select * from test;

        ID NAME
---------- --------------------
         1 yallonking
         2 yallonking
         3 yallonking
         4 oraking
         5 oraking
         6 oraking

6 rows selected.
--误删除了表中部分数据
SQL> delete from test where name='oraking';

3 rows deleted.
--提交误操作
SQL> commit;

Commit complete.
--尝试回滚数据
SQL> rollback;

Rollback complete.
--回滚数据无效
SQL> select * from test;

        ID NAME
---------- --------------------
         1 yallonking
         2 yallonking
         3 yallonking
--查看无操作的数据库时间
SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2012/04/03 20:00:10
--查看能够闪回的最早时间点
SQL> select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI
-------------------- -------------------
             1886318 2011-11-02 14:49:41
    --查看删除前的表记录
SQL> select * from test as of timestamp to_timestamp('2012/04/03 19:59:10','yyyy-mm-dd hh24:mi:ss');

        ID NAME
---------- --------------------
         1 yallonking
         2 yallonking
         3 yallonking
         4 oraking
         5 oraking
         6 oraking

6 rows selected.
    --查看已经删除掉的数据
SQL> select * from test as of timestamp to_timestamp('2012/04/03 19:59:10','yyyy-mm-dd hh24:mi:ss') minus select * from

test;

        ID NAME
---------- --------------------
         4 oraking
         5 oraking
             6 oraking
    --回填误删除的数据
SQL> insert into test  select * from test as of timestamp to_timestamp('2012/04/03 19:59:10','yyyy-mm-dd hh24:mi:ss')

minus select * from test;

3 rows created.
--确认回填数据正确
SQL> select * from test;

        ID NAME
---------- --------------------
         1 yallonking
         2 yallonking
         3 yallonking
         4 oraking
         5 oraking
         6 oraking

6 rows selected.
--提价回填数据操作
SQL> commit;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26143577/viewspace-720312/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26143577/viewspace-720312/