Oracle数据闪回

来源：互联网发布：java精简版编辑：程序博客网时间：2024/05/16 19:40

假如有表 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)

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

0 0