【闪回】查询

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

一、闪回查询 as of 子句

语法：

SELECT FROM AS 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_query as 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_query as 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博客 ” ，链接：http://blog.itpub.net/685769/viewspace-749348/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/685769/viewspace-749348/