闪回技术中包括：闪回查询，闪回表，闪回删除表，下面一一细说

一：闪回查询，就是如果你对一个表做了DML，并且已经commit，但是你想查看你修改之前的值，这时你就可以用闪回查询了

1,基于时间的闪回查询，

HR@ENMOEDU> select employee_id,first_name,last_name
2 from employees;

EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
100 Steven King
101 Neena Kochhar
102 Lex De Haan
103 Alexander Hunold
104 Bruce Ernst
105 David Austin
106 Valli Pataballa
107 Diana Lorentz
108 Nancy Greenberg
109 Daniel Faviet
110 John Chen

HR@ENMOEDU> delete from employees where employee_id=109;

1 row deleted.

HR@ENMOEDU> commit ；

Commit complete.

HR@ENMOEDU> select employee_id,first_name,last_name from employees as of timestamp sysdate - 1/1440; #查看一分钟前的表的信息 109 号记录还是有的。

EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
100 Steven King
101 Neena Kochhar
102 Lex De Haan
103 Alexander Hunold
104 Bruce Ernst
105 David Austin
106 Valli Pataballa
107 Diana Lorentz
108 Nancy Greenberg
109 Daniel Faviet
110 John Chen

2.基于scn的闪回查询，

SYS@ENMOEDU> grant execute on dbms_flashback to hr; #先授予hr用户来查询scn号的权限

Grant succeeded.

SYS@ENMOEDU> conn hr/hr

Connected.
HR@ENMOEDU> select dbms_flashback.get_system_change_number from dual; # 查询当前的scn号

GET_SYSTEM_CHANGE_NUMBER
------------------------
1235204

HR@ENMOEDU> delete from employees where employee_id=107;

1 row deleted.

HR@ENMOEDU> select employee_id,last_name from employees e
2 where e.employee_id <=110; #发现已经没有了107号

EMPLOYEE_ID LAST_NAME
----------- -------------------------
100 King
101 Kochhar
102 De Haan
103 Hunold
104 Ernst

105 David
106 Pataballa
108 Greenberg
110 Chen

9 rows selected.

HR@ENMOEDU> select employee_id,last_name from employees as of scn 1235204 where employee_id <=110; #发现又有的107号

EMPLOYEE_ID LAST_NAME
----------- -------------------------
100 King
101 Kochhar
102 De Haan
103 Hunold
104 Ernst

105 David
106 Pataballa
107 Lorentz
108 Greenberg
110 Chen

10 rows selected.

3.基于版本的闪回查询：

HR@ENMOEDU> update employees set last_name ='liuwen' where employee_id=202;
1 row updated.
HR@ENMOEDU> commit;
Commit complete.
HR@ENMOEDU> update employees set last_name ='liuwenhe' where employee_id=202;
1 row updated.
HR@ENMOEDU> commit;
Commit complete.
HR@ENMOEDU> update employees set last_name ='wenhe' where employee_id=202;
1 row updated.
HR@ENMOEDU>commit ;

Commit complete.
HR@ENMOEDU> select employee_id,last_name,versions_starttime,versions_endtime,versions_operation
2 from employees
3 versions between scn minvalue and maxvalue
4 where employee_id=202;

EMPLOYEE_ID LAST_NAME
----------- -------------------------
VERSIONS_STARTTIME
---------------------------------------------------------------------------
VERSIONS_ENDTIME V
--------------------------------------------------------------------------- -
202 wenhe
12-AUG-14 08.40.59 PM
U

202 liuwenhe
12-AUG-14 08.40.49 PM
12-AUG-14 08.40.59 PM U

EMPLOYEE_ID LAST_NAME
----------- -------------------------
VERSIONS_STARTTIME
---------------------------------------------------------------------------
VERSIONS_ENDTIME V
--------------------------------------------------------------------------- -

202 liuwen
12-AUG-14 08.40.40 PM
12-AUG-14 08.40.49 PM U

202 Fay

EMPLOYEE_ID LAST_NAME
----------- -------------------------
VERSIONS_STARTTIME
---------------------------------------------------------------------------
VERSIONS_ENDTIME V
--------------------------------------------------------------------------- -
12-AUG-14 08.40.40 PM

二：闪回表是说你只是删掉了表的一行或多行数据，但是表结构还在，这时候你是可以闪回到你没有删数据的状态的，

HR@ENMOEDU> select * from liu;

X
----------
2
1

HR@ENMOEDU> delete from liu where x=1;

1 row deleted.

HR@ENMOEDU> select * from liu;

X
----------
2

HR@ENMOEDU> flashback table liu to timestamp sysdate-5/1440;
flashback table liu to timestamp sysdate-5/1440
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled #是因为你的表行迁移开关未打开

HR@ENMOEDU> alter table liu enable row movement; #打开liu这个表的行迁移

Table altered.

然后再执行闪回表

HR@ENMOEDU> flashback table liu to timestamp sysdate-8/1440;

Flashback complete.

HR@ENMOEDU> select * from liu; #发现表确实又回到了删数据之前了

X
----------
2

1

三：闪回删除表: 可以理解为恢复，

HR@ENMOEDU> create table t (x int);

Table created.

HR@ENMOEDU> insert into t values(2);

1 row created.

HR@ENMOEDU> commit
2 ;

Commit complete.

HR@ENMOEDU> drop table t;

Table dropped.

HR@ENMOEDU> flashback table t to before drop rename to liu; 闪回并重新命名；

Flashback complete.

HR@ENMOEDU> select * from liu;

X
----------
2

ITPub博客

flashback闪回技术应用解析

最新文章