闪回表查询：
从概念上说，闪回表很简单。oracle将会查询撤销段以提取已更改的所有行，然后构造并执行将会取消更改的语句。闪回操作是一个单独的事务，如果可能，它就会抵消以前所有事务的效果。数据库仍然保持联机并且正常的运行不受影响，除非行锁定成为一个问题。
表闪回只是另一个事务，通常的规则仍适用。不同于正常处理的唯一之处是，表上的触发器对闪回操作默认是禁用的。
表闪回经常涉及一个存在外键关系的表，在这种情况下，几乎不可避免的是闪回操作会因为违反约束而失败。为了避免这个问题，语法支持用一条命令闪回多个表。
启用闪回的第一步是在表上支持行移动。
测试表employees和departments
向表中插入一行数据：
HR@orcl 08-OCT-14>insert into employees values(800,'WATSON','JANE','JWHALE','650.507.9833','13-JAN-08','SH_CLERK',2600,NULL,124,300);
1 row created.
1 row created.
查看当前的时间，注意要设置好时间格式，如果时间格式不正确，那么后面进行表闪回会出现找不到snapshot的错误：
HR@orcl 08-OCT-14>alter session set nls_date_format='yy-mm-dd hh24:mi:ss';
Session altered.
HR@orcl 14-10-08 07:26:28>select sysdate from dual;
SYSDATE
-----------------
14-10-08 07:26:33
Session altered.
HR@orcl 14-10-08 07:26:28>select sysdate from dual;
SYSDATE
-----------------
14-10-08 07:26:33
接下来删除该部门和员工，要注意先删除员工以避免违反约束：
HR@orcl 14-10-08 06:59:05>delete from departments where department_id=300;
delete from departments where department_id=300
*
ERROR at line 1:
ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated - child record found
delete from departments where department_id=300
*
ERROR at line 1:
ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated - child record found
HR@orcl 14-10-08 07:01:15>delete from employees where employee_id=800;
1 row deleted.
HR@orcl 14-10-08 07:01:56>delete from departments where department_id=300;
1 row deleted.
HR@orcl 14-10-08 07:02:11>commit;
Commit complete.
1 row deleted.
HR@orcl 14-10-08 07:01:56>delete from departments where department_id=300;
1 row deleted.
HR@orcl 14-10-08 07:02:11>commit;
Commit complete.
现在尝试将表闪回到该部门和员工存在的时刻：
HR@orcl 14-10-08 07:02:14>flashback table employees to timestamp to_timestamp('14-10-08 06:59:05','yy-mm-dd hh24:mi:ss');
flashback table employees to timestamp to_timestamp('14-10-08 06:59:05','yy-mm-dd hh24:mi:ss')
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
flashback table employees to timestamp to_timestamp('14-10-08 06:59:05','yy-mm-dd hh24:mi:ss')
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
可以看到该操作会失败，因为默认情况下没有为任何表启用行移动（作为表闪回的前提条件）：
HR@orcl 14-10-08 07:04:25>select table_name,row_movement from user_tables;
TABLE_NAME ROW_MOVE
------------------------------ --------
LOCATIONS DISABLED
LOC DISABLED
T DISABLED
RETRIED_EMPS DISABLED
REGIONS_BACKUP DISABLED
COUNTRIES DISABLED
SYS_TEMP_FBT DISABLED
EMPLOYEES DISABLED
REGIONS DISABLED
DEPARTMENTS DISABLED
JOB_HISTORY DISABLED
TABLE_NAME ROW_MOVE
------------------------------ --------
JOBS DISABLED
12 rows selected.
TABLE_NAME ROW_MOVE
------------------------------ --------
LOCATIONS DISABLED
LOC DISABLED
T DISABLED
RETRIED_EMPS DISABLED
REGIONS_BACKUP DISABLED
COUNTRIES DISABLED
SYS_TEMP_FBT DISABLED
EMPLOYEES DISABLED
REGIONS DISABLED
DEPARTMENTS DISABLED
JOB_HISTORY DISABLED
TABLE_NAME ROW_MOVE
------------------------------ --------
JOBS DISABLED
12 rows selected.
HR@orcl 14-10-08 07:05:04>alter table employees enable row movement;
Table altered.
HR@orcl 14-10-08 07:06:37>alter table departments enable row movement;
Table altered.
Table altered.
HR@orcl 14-10-08 07:06:37>alter table departments enable row movement;
Table altered.
HR@orcl 14-10-08 07:06:48>select table_name,row_movement from user_tables;
TABLE_NAME ROW_MOVE
------------------------------ --------
LOCATIONS DISABLED
LOC DISABLED
T DISABLED
RETRIED_EMPS DISABLED
REGIONS_BACKUP DISABLED
COUNTRIES DISABLED
SYS_TEMP_FBT DISABLED
EMPLOYEES ENABLED
REGIONS DISABLED
DEPARTMENTS ENABLED
JOB_HISTORY DISABLED
TABLE_NAME ROW_MOVE
------------------------------ --------
JOBS DISABLED
12 rows selected.
TABLE_NAME ROW_MOVE
------------------------------ --------
LOCATIONS DISABLED
LOC DISABLED
T DISABLED
RETRIED_EMPS DISABLED
REGIONS_BACKUP DISABLED
COUNTRIES DISABLED
SYS_TEMP_FBT DISABLED
EMPLOYEES ENABLED
REGIONS DISABLED
DEPARTMENTS ENABLED
JOB_HISTORY DISABLED
TABLE_NAME ROW_MOVE
------------------------------ --------
JOBS DISABLED
12 rows selected.
再次尝试闪回：
HR@orcl 14-10-08 07:28:46>flashback table employees to timestamp to_timestamp('14-10-08 07:26:33','yy-mm-dd hh24:mi:ss');
flashback table employees to timestamp to_timestamp('14-10-08 07:26:33','yy-mm-dd hh24:mi:ss')
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (HR.EMP_DEPT_FK) violated - parent key not found
flashback table employees to timestamp to_timestamp('14-10-08 07:26:33','yy-mm-dd hh24:mi:ss')
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (HR.EMP_DEPT_FK) violated - parent key not found
这一次造成失败的原因更加微妙。闪回试图通过插入此员工来取消对员工800的删除---但是员工800属于部门300，已经删除了该部门而不存在它。因此，违反了外键约束。要避免此问题可以首先闪回departments表，这样可以插入部门300.但是，如果闪回涉及多个表和许多DML语句，那么在逻辑上很难找到一个生效的顺序。解决方法就是一起闪回两个表：
HR@orcl 14-10-08 07:30:39>flashback table employees,departments to timestamp to_timestamp('14-10-08 07:26:33','yy-mm-dd hh24:mi:ss');
Flashback complete.
Flashback complete.
这样闪回会成功，因为一个事务中同时闪回了两张表，并且仅在该事务的结尾检查约束---到那时候数据在逻辑上是一致的。
HR@orcl 14-10-08 07:33:48>select * from employees where employee_id=800;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ----------------- ---------- ---------- -------------- ---------- -------------
800 WATSON JANE JWHALE 650.507.9833 13-01-08 00:00:00 SH_CLERK 2600 124 300
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ----------------- ---------- ---------- -------------- ---------- -------------
800 WATSON JANE JWHALE 650.507.9833 13-01-08 00:00:00 SH_CLERK 2600 124 300
HR@orcl 14-10-08 07:36:24>select * from departments where department_id=300;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
300 SUPPORT 1700
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
300 SUPPORT 1700
闪回也可能由于其他原因失败：
1.如果在删除和闪回之间重用了键值，就会出现主键约束。
2.如果没有足够的撤销信息返回到请求的时间，就会引起一个错误ORA-08180，"No snapshot found based on specified time"。
3.如果其他用户锁定闪回用户的任何行，则闪回会失败并给出一条信息：ORA-00054："Resource busy and acquire with NOWAIT specified"。
4.表定义在考察期间内不能改变-------闪回不能跨越DDL，试图这样做会产生错误：ORA-01446:"Uable to read data---table definition has changed".
5.闪回不适用于处于SYS模式下的表，试着想象一下闪回部分数据字典的结果。
如果闪回由于任何原因失败了，就会取消闪回操作：将回滚成功执行的任何一部分操作，并且表将处于闪回命令发出之前的状态。