闪回查询可以依据时间或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博客 ” ，链接：https://blog.itpub.net/685769/viewspace-749348/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
