flashback技术之---flashback query

--创建测试表并分不同时间插入数据，记录插入之后的时间。

SQL> create table t(id number,ins_date date) tablespace users;

Table created.

SQL> insert into t values(1,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select sysdate from dual;

SYSDATE

-----------------

20120718 05:10:28

SQL> insert into t values(2,sysdate);

1 row created.

SQL>  commit;

Commit complete.

SQL> select sysdate from dual;

SYSDATE

-----------------

20120718 05:11:20

SQL> insert into t values(3,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL>  select sysdate from dual;

SYSDATE

-----------------

20120718 05:12:50

--开始执行闪回查询，分别根据上面记录的时间来查询不同时间点的数据

SQL> select * from t;

        ID INS_DATE

---------- -----------------

         1 20120718 05:09:55

         2 20120718 05:10:36

         3 20120718 05:11:30

SQL> SELECT * FROM t AS OF TIMESTAMP  TO_TIMESTAMP('20120718 05:10:28', 'yyyymmdd hh24:mi:ss');

        ID INS_DATE

---------- -----------------

         1 20120718 05:09:55

SQL> SELECT * FROM t AS OF TIMESTAMP  TO_TIMESTAMP('20120718 05:11:20', 'yyyymmdd hh24:mi:ss');

        ID INS_DATE

---------- -----------------

         1 20120718 05:09:55

         2 20120718 05:10:36

SQL> SELECT * FROM t AS OF TIMESTAMP  TO_TIMESTAMP('20120718 05:12:50', 'yyyymmdd hh24:mi:ss');

        ID INS_DATE

---------- -----------------

         1 20120718 05:09:55

         2 20120718 05:10:36

         3 20120718 05:11:30

也可以通过scn来执行闪回查询，这个是最精确的：

SQL> truncate table t;

Table truncated.

SQL> insert into t values(1,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

                  310281

SQL> insert into t values(2,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

                  310286

SQL> insert into t values(3,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

                  310291

SQL> select * from t;

        ID INS_DATE

---------- -----------------

         1 20120718 05:23:48

         2 20120718 05:23:59

         3 20120718 05:24:12

SQL> SELECT * FROM t AS OF scn 310281;

        ID INS_DATE

---------- -----------------

         1 20120718 05:23:48

SQL> SELECT * FROM t AS OF scn  310286;

        ID INS_DATE

---------- -----------------

         1 20120718 05:23:48

         2 20120718 05:23:59

SQL> SELECT * FROM t AS OF scn  310291;

        ID INS_DATE

---------- -----------------

         1 20120718 05:23:48

         2 20120718 05:23:59

         3 20120718 05:24:12

同样，采用dbms_flashback包，也可以实现上述查询，dbms_flashback的功能就是将时钟回退到指定的时间点或scn，然后就可以通过普通查询的方式来查询数据：

SQL> create table t(id number,ins_date date) tablespace users;

Table created.

SQL> insert into t values(1,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

                  310565

SQL> insert into t values(2,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

                  310570

SQL> insert into t values(3,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

                  310576

--开始执行闪回查询：

SQL>  exec DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(310565);

PL/SQL procedure successfully completed.

SQL> select  * from t;

        ID INS_DATE

---------- -----------------

         1 20120718 05:36:57

SQL> exec DBMS_FLASHBACK.DISABLE;

PL/SQL procedure successfully completed.

SQL> exec DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(310570);

PL/SQL procedure successfully completed.

SQL>  select  * from t;

        ID INS_DATE

---------- -----------------

         1 20120718 05:36:57

         2 20120718 05:37:18

SQL> exec DBMS_FLASHBACK.DISABLE;

PL/SQL procedure successfully completed.

SQL> exec DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(310576);

PL/SQL procedure successfully completed.

SQL> select  * from t;

        ID INS_DATE

---------- -----------------

         1 20120718 05:36:57

         2 20120718 05:37:18

         3 20120718 05:37:31

SQL> exec DBMS_FLASHBACK.DISABLE;

PL/SQL procedure successfully completed.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10972173/viewspace-738210/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10972173/viewspace-738210/