Oracle数据闪回

假如有表 TEMP

SQL> SELECT * FROM TEMP;

        ID INFO
---------- --------------------------------------------------------------------------------
         1 aaa
         2 bbb
         3 ccc

SQL> DELETE FROM TEMP ID = 1;

1 row deleted

SQL> COMMIT;

Commit complete

SQL> SELECT * FROM TEMP;

        ID INFO
---------- --------------------------------------------------------------------------------
         2 bbb
         3 ccc

 

恢复数据

SQL> FLASHBACK TABLE TEMP TO TIMESTAMP TO_TIMESTAMP('2010-9-9 10:16:3','yyyy-mm-dd hh24:mi:ss');

ORA-08189: 因为未启用行移动功能, 不能闪回表
SQL> ALTER TABLE TEMP ENABLE ROW MOVEMENT;

Table altered

SQL> FLASHBACK TABLE TEMP TO TIMESTAMP TO_TIMESTAMP('2010-9-9 10:16:3','yyyy-mm-dd hh24:mi:ss');

 

SQL> SELECT * FROM TEMP;

 

       ID INFO
---------- --------------------------------------------------------------------------------
         1 aaa
         2 bbb
         3 ccc

drop表后的恢复
SQL> DROP TABLE TEMP;

Table dropped

SQL> SELECT * FROM TEMP;

ORA-00942: 表或视图不存在

SQL> FLASHBACK TABLE TEMP TO BEFORE DROP;

 

SQL> SELECT * FROM TEMP;

        ID INFO
---------- --------------------------------------------------------------------------------
         1 aaa
         2 bbb
         3 ccc 

 

====================================================

另一方案：

--将历史数据已临时表展现出来
SELECT * FROM < TABLE_NAME > AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '15' MINUTE)

 

至于这两种方法有何不同，这里不做说明了。