oracle闪回表介绍

CREATE TABLE employees_demo AS SELECT * FROM employees;

 

SELECT salary  FROM employees_test  WHERE salary < 2500;    SALARY----------      2400      2200      2100      2400      2200

注：由于是新建的表，在使用以下闪回语句之前需要等待至少5分钟让SCN传送到被闪回的映像表上。

 

ALTER TABLE employees_test   ENABLE ROW MOVEMENT;

 

UPDATE employees_test  SET salary = salary * 1.1  WHERE salary < 2500;5 rows updated.COMMIT;

 

SELECT salary  FROM employees_test  WHERE salary < 2500;    SALARY----------      2420      2310      2420

 

 闪回表到更新数据之前的状态：

SQL> flashback table employees_test  2  to timestamp to_timestamp ('2012-08-02 19:12:20','yyyy-mm-dd hh24:mi:ss');闪回完成。

 

SQL> select salary from employees_test where salary < 2500;    SALARY----------      2400      2200      2100      2400      2200

2. 恢复被删除的表，通过表空间的Recyclebin（回收站）实现找回被删除的数据：

SQL> drop table employees_test;表已删除。SQL> select object_name, original_name, operation, type, droptime from recyclebin;OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE                      DROPTIME------------------------------ -------------------------------- --------- ------------------------- -------------------BIN$Bj8Ym5tKQR6kWeBVn9y0fA==$0 EMPLOYEES_TEST                   DROP      TABLE                     2012-08-02:19:26:54

 

SQL> flashback table employees_test to before drop;闪回完成。SQL> select object_name, original_name, operation, type, droptime from recyclebin;未选定行

 

SQL> drop table employees_test;表已删除。SQL> flashback table employees_test to before drop rename to employees_demo;闪回完成。SQL> select * from employees_test;select * from employees_test              *第 1 行出现错误:ORA-00942: 表或视图不存在

 

 

 

以上内容为简单的闪回表操作，内容来自oracle官方文档。

 

 

 

made by dylan.