flashback技術之---flashback query

lsq_008發表於2012-07-29
--建立測試表並分不同時間插入資料，記錄插入之後的時間。
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章