Oracle 闪回特性(Flashback Query、Flashback Table)

傷城~ 2022-09-18 13:46 156阅读 0赞

\-- Oracle 闪回特性(Flashback Query、Flashback Table)

\--==================================================

Oracle 闪回查询是指针对特定的表来查询特定的时间段内的数据变化情况来确定是否将表闪回到某一个特定的时刻以保证数据无讹误存在。

这个特性大大的减少了采用时点恢复所需的工作量以及数据库脱机的时间。  闪回查询通常分为Flashback Query(闪回查询)，

Flashback Table Query(闪回表查询)，Flashback Version Query(闪回版本查询)，Flashback Transaction Query(闪回事务查询)。本文主要讲

述Flashback Query(闪回查询)，Flashback Table Query(闪回表查询)。其余闪回请参考后续文章。

一、Flashback Query(闪回查询)

通常用于检索一条记录的所有版本，倒退单独的事务或者倒退从指定时间以来对特定表的所有变化

Flashback Query的所有形式取决于UNDO表表空间，关于UDNO表空间请参考：[Oracle 回滚(ROLLBACK)][Oracle _ROLLBACK]和撤销(UNDO)

1.闪回查询(Flashback Query)语法

SELECT <column\_name\_list>

FROM <table\_name>

AS OF <SCN>                            \--使用as of scn

\[WHERE <filter\_conditions>\]

\[GROUP BY <unaggregated columns>\]

\[HAVING <group\_filter>\]

\[ORDER BY <column\_positions\_or\_name>\]

SELECT <column\_name\_list>

FROM <table\_name>

AS OF <TIMESTAMP>                      \--使用as of timestamp

\[WHERE <filter\_conditions>\]

\[GROUP BY <unaggregated columns>\]

\[HAVING <group\_filter>\]

\[ORDER BY <column\_positions\_or\_name>\]

2.演示闪回查询

a.演示使用as of timestamp来进行闪回查询

flasher@ORCL11G> create table tb1  as

2   select empno,ename,job,deptno from scott.emp  where 1=0;

flasher@ORCL11G> insert into tb1

2   select empno,ename,job,deptno

3   from scott.emp where empno in(7369,7499,7521,7566);

flasher@ORCL11G> commit;

flasher@ORCL11G> select \*  from tb1;

EMPNO ENAME      JOB           DEPTNO

\---------- ---------- --------- ----------

7369 SMITH      CLERK             20

7499 ALLEN      SALESMAN          30

7521 WARD       SALESMAN          30

7566 JONES      MANAGER           20

flasher@ORCL11G> select to\_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO\_CHAR(SYSDATE,'YY'

\-------------------

2010\-10\-25 17:26:08

flasher@ORCL11G> delete from tb1  where job='SALESMAN';

flasher@ORCL11G> commit;

flasher@ORCL11G> select \*  from tb1;

EMPNO ENAME      JOB           DEPTNO

\---------- ---------- --------- ----------

7369 SMITH      CLERK             20

7566 JONES      MANAGER           20

flasher@ORCL11G> select \*  from tb1 as of timestamp

2  to\_timestamp('2010-10-25 17:26:08','yyyy-mm-dd hh24:mi:ss');

EMPNO ENAME      JOB           DEPTNO

\---------- ---------- --------- ----------

7369 SMITH      CLERK             20

7499 ALLEN      SALESMAN          30

7521 WARD       SALESMAN          30

7566 JONES      MANAGER           20

flasher@ORCL11G> select \*  from tb1 as of timestamp

2  to\_timestamp('2010-10-25 17:26:08','yyyy-mm-dd hh24:mi:ss')

3  minus  select \* from tb1;

EMPNO ENAME      JOB           DEPTNO

\---------- ---------- --------- ----------

7499 ALLEN      SALESMAN          30

7521 WARD       SALESMAN          30

b.演示使用as of scn来进行闪回查询

flasher@ORCL11G> select current\_scn from v$database;

CURRENT\_SCN

\-----------

2032782

flasher@ORCL11G> select \*  from tb1;

EMPNO ENAME      JOB           DEPTNO

\---------- ---------- --------- ----------

7369 Henry      CLERK             20

7566 JONES      MANAGER           20

flasher@ORCL11G> delete from tb1  where empno=7369;

flasher@ORCL11G> commit;

flasher@ORCL11G> select \*  from tb1 as of scn 2032782;

EMPNO ENAME      JOB           DEPTNO

\---------- ---------- --------- ----------

7369 Henry      CLERK             20

7566 JONES      MANAGER           20

由以上可知，通过闪回查询获得所需的记录信息，然后来构造新的DML语句并实施其操作来保证数据的完整性。

二、Flashback Table Query(闪回表查询)

通过查询UNDO段来抽取所有已变化的记录细节，在此基础之上再构造和执行能够倒退这些变化的语句

表闪回通过执行倒退变化的语句并且该执行是一个事务，所有常用规则在该事务上起作用。

表闪回时，表上的触发器缺省被禁用，即该表上的DML触发器将暂时失效，可以在闪回时指定触发器是否失效。

表闪回需要启用表上的记录转移选项

1.下面给出表闪回的种方式

FLASHBACK  TABLE <schema\_name.table\_name>

TO SCN <scn\_number>            \--基于SCN的表闪回

\[<ENABLE | DISABLE> TRIGGERS\]

FLASHBACK  TABLE <schema\_name.table\_name>

TO TIMESTAMP <timestamp>       \--基于TIMESTAMP的表闪回

\[<ENABLE | DISABLE> TRIGGERS\]

FLASHBACK  TABLE <schema\_name.table\_name>

TO RESTORE POINT <restore\_point>   \--基于RESTORE POINT的表闪回

\[<ENABLE | DISABLE> TRIGGERS\]

2.演示基于SCN的表闪回

下面的演示首先创建表tb\_tables，并对表分几次插入数据，在完成插入前记录其SCN号用于后续对其进行闪回

create table tb\_emp as          \--创建演示表tb\_emp

select empno,ename,job,deptno from scott.emp  where 1=0;

select table\_name,row\_movement from user\_tables; \--查看表的row movement行为，缺省为disable

TABLE\_NAME                     ROW\_MOVE

\------------------------------ --------

TB\_EMP                         DISABLED

select current\_scn,systimestamp from v$database;  \--获取系统当前的SCN

CURRENT\_SCN SYSTIMESTAMP

\----------- --------------------------------------

661490 01\-JAN\-11 10.56.28.733000 PM \+08:00

insert into tb\_emp         \--插入deptno为10的员工

select empno,ename,job,deptno from scott.emp  where deptno=10;

commit;

select current\_scn,systimestamp from v$database;  \--获取系统当前的SCN

CURRENT\_SCN SYSTIMESTAMP

\----------- --------------------------------------

661510  01\-JAN\-11 10.56.56.546000 PM \+08:00

insert into tb\_emp        \--插入deptno为20的员工

select empno,ename,job,deptno from scott.emp  where deptno=20;

commit;

select current\_scn,systimestamp from v$database;  \--获取系统当前的SCN

CURRENT\_SCN SYSTIMESTAMP

\----------- --------------------------------------

661521  01\-JAN\-11 10.57.17.358000 PM \+08:00

insert into tb\_emp       \--插入deptno为30的员工

select empno,ename,job,deptno from scott.emp  where deptno=30;

commit;

select current\_scn,systimestamp from v$database;  \--获取系统当前的SCN

CURRENT\_SCN SYSTIMESTAMP

\----------- --------------------------------------

661539 01\-JAN\-11 10.57.37.843000 PM \+08:00

select deptno,count(\*) from tb\_emp group by deptno order  by 1;

DEPTNO    COUNT(\*)

\---------- ----------

10          3

20          5

30          6

flashback  table tb\_emp to scn  661521;    \--将表闪回到scn为，即插入部门号为的记录之前

flashback  table tb\_emp to scn 661521       \--闪回失败，收到错误提示，没有开启row movement

ERROR at line 1:

ORA\-08189: cannot flashback the table because row movement  is not enabled

alter table tb\_emp enable row movement;   \--开启表tb\_emp表的row movement 功能

flashback  table tb\_emp to scn  661521;    \--再次实施闪回，闪回成功

select deptno,count(\*) from tb\_emp group by deptno order  by 1;  \--记录中没有部门为30的记录

DEPTNO    COUNT(\*)

\---------- ----------

10          3

20          5

flashback  table tb\_emp to scn  661510;    \--将表闪回到scn为，即插入部门号为20的记录之前

select deptno,count(\*) from tb\_emp group by deptno order  by 1;  \--记录中没有部门为20的记录

DEPTNO    COUNT(\*)

\---------- ----------

10          3

3.演示基于TIMESTAMP的表闪回

使用to  timestamp进行表闪回,继续使用上面创建的表来进行闪回

\--使用timestamp将表闪回到插入部门号10为之前

flashback  table tb\_emp to timestamp  to\_timestamp('01-JAN-11 10.56.28.733000');

flashback  table tb\_emp to timestamp  to\_timestamp('01-JAN-11 10.56.28.733000') \--收到错误提示

ERROR at line 1:

ORA\-01466: unable to read data  \- table definition has changed   \--表结构发生改变

flasher@ORCL11G> flashback table tb\_emp to scn 661539;  \--可以将表闪回到插入部门号为30的记录之后

Flashback complete.

此处演示中收到了错误提示，注意对于表闪回，可以多次使用同一类型的闪回方式，可以往前闪回，一旦往前闪回之后，也可以往后进行闪回。

但交叉闪回则提示表定义发生了变化。闪回失败。我们可以再次创建一张类似的新表进行基于timestamp进行闪回，与闪回SCN说不同的

是，此处使用了timestamp,此演示在此省略。

4.演示基于RESTORE POINT的表闪回

基于RESTORE POINT的表闪回首先要创建适当的闪回点，创建闪回点的方式为

CREATE  RESTORE POINT point\_name;

对于闪回成功之后，无用的闪回点可以及时删除掉，删除闪回点的方式为

DROP  RESTORE POINT point\_name

下面对基于RESTORE POINT 闪回进行演示

drop table tb\_emp purge;  \--删除先前创建的表tb\_emp

create table tb\_emp    \--创建演示表tb\_emp

enable row movement

as select empno,ename,job,deptno from scott.emp  where 1=0;

create restore point zero;   \--创建闪回点zero

insert into tb\_emp           \--插入deptno为10的员工

select empno,ename,job,deptno from scott.emp  where deptno=10;

commit;

create restore point one;    \--创建闪回点one

insert into tb\_emp           \--插入deptno为20的员工

select empno,ename,job,deptno from scott.emp  where deptno=20;

commit;

create restore point two;    \--创建闪回点two

insert into tb\_emp           \--插入deptno为30的员工

select empno,ename,job,deptno from scott.emp  where deptno=30;

commit;

select deptno,count(\*) from tb\_emp group by deptno order  by 1;

DEPTNO    COUNT(\*)

\---------- ----------

10          3

20          5

30          6

flashback  table tb\_emp to restore point two;   \--闪回到闪回点two之前

select deptno,count(\*) from tb\_emp group by deptno order  by 1;

flashback  table tb\_emp to restore point one;   \--闪回到闪回点one之前

select deptno,count(\*) from tb\_emp group by deptno order  by 1;

DEPTNO    COUNT(\*)

\---------- ----------

10          3

drop restore point two;   \--删除创建的闪回点

drop restore point one;

drop restore point zero;

5.存在参照关系的表闪回

帐户flasher中表tb1与表tb2存在外键关系，表tb1的deptno 参照了表tb2中的deptno列

帐户flasher中表tb1与scott.emp具有相同的表结构，表tb2与表scott.dept具有相同的表结构

下面为表tb2新增一个deptno，且为表tb1新增一条记录

create table tb1       \--基于表scott.emp来创建表tb1

enable row movement

as select \*  from scott.emp ;

create table tb2       \--基于表scott.dept来创建表tb2

enable row movement

as select \*  from scott.dept;

alter table tb1        \--为表tb1添加主键约束

add constraint tb1\_empno\_pk  primary key(empno);

alter table tb2        \--为表tb2添加主键约束

add constraint tb2\_deptno\_pk  primary key(deptno);

alter table tb1        \--为表tb1添加外键约束

add constraint tb1\_tb2\_deptno\_fk  foreign key(deptno)

references tb2(deptno);

insert into tb2        \--为表tb2插入一个新部门

select 50,'Customer','Landon' from dual;

insert into tb1(empno,ename,job,deptno)   \--为表tb1插入一个新的雇员

select 8000,'Robinson','Clerk',50 from dual;

commit;

select current\_scn from v$database;   \--获得当前的scn

\--- 687444

delete from tb1 where empno=8000;     \--删除先前新增的部门

delete from tb2 where deptno=50;      \--删除先前新增的雇员

commit;

flashback  table tb1 to scn 687444;    \--闪回先前删除的雇员

/\*

ERROR at line 1:

ORA-02091: transaction rolled back    --提示事务被回滚，外键没有找到

ORA-02291: integrity constraint (FLASHER.TB1\_TB2\_DEPTNO\_FK) violated - parent key not found \*/

flashback  table tb1,tb2 to scn 687444;   \--将两个表同时闪回

select empno,ename,deptno,dname          \--此时新增的雇员被闪回，部门也被闪回

from tb1

inner join tb2

using(deptno)

where deptno=50;

EMPNO ENAME          DEPTNO DNAME

\---------- ---------- ---------- --------------

8000 Robinson           50 Customer

6.表闪回的几个常见问题

a.当闪回删除操作之前，如果某个键值如主键被重用，将导致违反主键约束，闪回失败。

b.若闪回所需要的UNDO信息不存在，将引发ORA-08180：no snapshot found based on specified time(未找到基于指定时间的快照)错误

c.如果受闪回影响的记录被其它用户锁定，将引发ORA-00054：resource busy and acquire with NOWAIT specified (资源忙碌)错误

d.表定义在闪回期间不能发生变化，否则导致ORA-01466：unable to read data - table definition has changed(表定义已变化)错误

e.闪回前未启用row movement，将收到ORA-08189: cannot flashback the table because row movement is not enabled 错误

f.对于存在参照关系的情况，建议将主表等一起实施闪回，否则，将收到ORA-02091: transaction rolled back，ORA-02291错误

g.SYS 模式中的表不能使用表闪回技术

[Oracle _ROLLBACK]: http://blog.csdn.net/robinson_0612/archive/2010/07/13/5731158.aspx

Oracle 闪回特性(Flashback Query、Flashback Table)

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Oracle 闪回特性(Flashback Version、Flashback Transaction)

相关 Oracle 闪回特性(Flashback Query、Flashback Table)

相关 Oracle 闪回特性(FLASHBACK DROP & RECYCLEBIN)

相关 Oracle 闪回特性(FLASHBACK DATABASE)

相关 Oracle Flashback(闪回) 详解.

相关 Oracle 闪回 flashback

相关 Oracle 闪回特性(Flashback Version、Flashback Transaction)

相关 Oracle 闪回特性(Flashback Query、Flashback Table)

相关 Oracle 闪回特性(FLASHBACK DROP & RECYCLEBIN)

相关 Oracle 闪回特性(FLASHBACK DATABASE)