flashback技术之---flashback query

最新推荐文章于 2021-04-13 12:40:34 发布

转载最新推荐文章于 2021-04-13 12:40:34 发布 · 93 阅读

本文详细介绍了如何使用SQL进行数据插入、记录插入时间，并通过系统更改编号（SCN）和DBMS_FLASHBACK包执行闪回查询，实现根据特定时间点查询数据库中的历史数据。同时，展示了通过TO_TIMESTAMP函数结合特定时间戳查询数据的方法。

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

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/