--创建测试表并分不同时间插入数据，记录插入之后的时间。
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博客 ” ，链接：https://blog.itpub.net/10972173/viewspace-738210/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
