闪回查询可以依据时间或SCN两种方式进行闪回查询（select as of 语句或PL/SQL闪回查询）

一、闪回查询 as of 子句

语法：

SELECTFROMAS OF

1)as of timestamp

SCOTT@ORA10G>set time on

14:57:59 SCOTT@ORA10G>create table fb_query as select * from dept;

Table created.

14:58:04 SCOTT@ORA10G>select * from fb_query;

DEPTNO DNAME LOC

———- ————– ————-

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

14:58:28 SCOTT@ORA10G>

14:59:50 SCOTT@ORA10G>delete fb_query where deptno=10;

1 row deleted.

14:59:55 SCOTT@ORA10G>commit;

Commit complete.

14:59:57 SCOTT@ORA10G>select * from fb_query;

DEPTNO DNAME LOC

———- ————– ————-

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

15:00:24 SCOTT@ORA10G>

–闪回查询1分钟以前

15:00:40 SCOTT@ORA10G>select * from fb_queryas of timestamp sysdate – 1/1440;

DEPTNO DNAME LOC

———- ————– ————-

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

15:00:46 SCOTT@ORA10G>

2)as of SCN

SCOTT@ORA10G>conn / as sysdba

Connected.

SYS@ORA10G>grant execute on dbms_flashback to scott;

Grant succeeded.

SYS@ORA10G>conn scott/tiger

Connected.

SCOTT@ORA10G>select dbms_flashback.get_system_change_number as scn from dual;

SCN

———-

644478

SCOTT@ORA10G>select * from fb_query;

DEPTNO DNAME LOC

———- ————– ————-

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SCOTT@ORA10G>delete fb_query where deptno >=30;

2 rows deleted.

SCOTT@ORA10G>commit;

Commit complete.

SCOTT@ORA10G>select * from fb_query;

DEPTNO DNAME LOC

———- ————– ————-

20 RESEARCH DALLAS

SCOTT@ORA10G>select * from fb_queryas of scn 644478;

DEPTNO DNAME LOC

———- ————– ————-

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SCOTT@ORA10G>

二、利用PL/SQL包dbms_flashback

语法：

会话启用闪回指定时间：

DBMS_FLASHBACK.ENABLE_AT_TIME(query_time IN TIMESTAMP);

会话启用闪回指定SCN:

DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(query_scn IN NUMBER);

关闭闪回：

DBMS_FLASHBACK.DISABLE;

1)指定时间

SCOTT@ORA10G>set time on

15:15:20 SCOTT@ORA10G>create table fb_query1 as select * from dept;

Table created.

15:15:31 SCOTT@ORA10G>create table fb_query2 as select * from dept;

Table created.

15:15:35 SCOTT@ORA10G>

15:16:35 SCOTT@ORA10G>update fb_query1 set loc=null;

4 rows updated.

15:17:21 SCOTT@ORA10G>update fb_query2 set dname=null;

4 rows updated.

15:17:26 SCOTT@ORA10G>commit;

Commit complete.

15:17:32 SCOTT@ORA10G>select * from fb_query1;

DEPTNO DNAME LOC

———- ————– ————-

10 ACCOUNTING

20 RESEARCH

30 SALES

40 OPERATIONS

15:17:38 SCOTT@ORA10G>select * from fb_query2;

DEPTNO DNAME LOC

———- ————– ————-

10 NEW YORK

20 DALLAS

30 CHICAGO

40 BOSTON

15:17:42 SCOTT@ORA10G>

闪回定位到1分钟前；另，处于闪回会话模式时，不允许执行DML、DDL操作

（注：包dbms_flashback的执行权限，要单独赋予）

15:17:43 SCOTT@ORA10G>exec dbms_flashback.enable_at_time(sysdate-1/1440);

PL/SQL procedure successfully completed.

15:18:06 SCOTT@ORA10G>select * from fb_query1;

DEPTNO DNAME LOC

———- ————– ————-

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

15:18:28 SCOTT@ORA10G>select * from fb_query2;

DEPTNO DNAME LOC

———- ————– ————-

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

15:18:32 SCOTT@ORA10G>delete fb_query1;

delete fb_query1

*

ERROR at line 1:

ORA-08182: operation not supported while in Flashback mode

15:18:40 SCOTT@ORA10G>exec dbms_flashback.disable;

PL/SQL procedure successfully completed.

15:18:56 SCOTT@ORA10G>select * from fb_query1;

DEPTNO DNAME LOC

———- ————– ————-

10 ACCOUNTING

20 RESEARCH

30 SALES

40 OPERATIONS

15:19:03 SCOTT@ORA10G>select * from fb_query2;

DEPTNO DNAME LOC

———- ————– ————-

10 NEW YORK

20 DALLAS

30 CHICAGO

40 BOSTON

15:19:07 SCOTT@ORA10G>

2)指定SCN

SCOTT@ORA10G>select dbms_flashback.get_system_change_number as scn from dual;

SCN

———-

645510

SCOTT@ORA10G>delete fb_query1;

4 rows deleted.

SCOTT@ORA10G>delete fb_query2;

4 rows deleted.

SCOTT@ORA10G>commit;

Commit complete.

SCOTT@ORA10G>exec dbms_flashback.enable_at_system_change_number(645510);

PL/SQL procedure successfully completed.

SCOTT@ORA10G>select * from fb_query1;

DEPTNO DNAME LOC

———- ————– ————-

10 ACCOUNTING

20 RESEARCH

30 SALES

40 OPERATIONS

SCOTT@ORA10G>select * from fb_query2;

DEPTNO DNAME LOC

———- ————– ————-

10 NEW YORK

20 DALLAS

30 CHICAGO

40 BOSTON

SCOTT@ORA10G>exec dbms_flashback.disable;

PL/SQL procedure successfully completed.

SCOTT@ORA10G>select count(*) from fb_query1;

COUNT(*)

———-

0

SCOTT@ORA10G>select count(*) from fb_query2;

COUNT(*)

———-

0

SCOTT@ORA10G>

三：小结

1）闪回查询也是依据UNDO数据来完成的，也就是说，能闪回查询多长时间的数据完全取决于undo_retention。

2）闪回查询可基于时间或SCN

3）闪回查询可以使用as of子句，也可使用PL/SQL包dbms_flashback

ITPub博客

闪回查询（五）

最新文章