闪回表查询:
从概念上说,闪回表很简单。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模式下的表,试着想象一下闪回部分数据字典的结果。
如果闪回由于任何原因失败了,就会取消闪回操作:将回滚成功执行的任何一部分操作,并且表将处于闪回命令发出之前的状态。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29800581/viewspace-1314431/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29800581/viewspace-1314431/