10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
实验三：闪回查询 as of scn
TYGER@ORCL>conn / as sysdba
Connected.
SYS@ORCL>grant execute on dbms_flashback to tyger;
Grant succeeded.
TYGER@ORCL>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1107246
TYGER@ORCL>select * from fb_tyger;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
TYGER@ORCL>delete fb_tyger where deptno<=30;
3 rows deleted.
TYGER@ORCL>commit;
Commit complete.
TYGER@ORCL>select * from fb_tyger;
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
TYGER@ORCL>select * from fb_tyger as of scn 1107246;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
实验四：利用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；
TYGER@ORCL>conn / as sysdba
Connected.
SYS@ORCL>grant execute on dbms_flashback to tyger;
Grant succeeded.
SYS@ORCL>conn tyger/tyger
Connected.
TYGER@ORCL>
TYGER@ORCL>
TYGER@ORCL>
TYGER@ORCL>create table fb_query1 as select * from scott.dept;
Table created.
TYGER@ORCL>create table fb_query2 as select * from scott.dept;
Table created.
TYGER@ORCL>commit;
Commit complete.
TYGER@ORCL>select * from fb_query1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
TYGER@ORCL>select * from fb_query2;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
TYGER@ORCL>set time on;
11:03:38 TYGER@ORCL>update fb_query1 set loc='';
4 rows updated.
11:03:52 TYGER@ORCL>commit;
Commit complete.
11:03:54 TYGER@ORCL>update fb_query2 set dname='';
4 rows updated.
11:04:14 TYGER@ORCL>commit;
Commit complete.
11:04:15 TYGER@ORCL>
11:04:15 TYGER@ORCL>select * from fb_query1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
11:04:23 TYGER@ORCL>select * from fb_query2;
DEPTNO DNAME LOC
---------- -------------- -------------
10 NEW YORK
20 DALLAS
30 CHICAGO
40 BOSTON
// 闪回定位到5分钟前，此时若访问sysdate等时间函数，那么返回的是当前值而非5分钟之前。
11:04:30 TYGER@ORCL>exec dbms_flashback.enable_at_time(sysdate-5/1440);
PL/SQL procedure successfully completed.
11:05:09 TYGER@ORCL>select * from fb_query1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
11:05:29 TYGER@ORCL>select * from fb_query2;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
//处于闪回会话模式时，不允许执行DML 、 DDL 操作
11:05:45 TYGER@ORCL>update fb_query1 set dname='';
update fb_query1 set dname=''
*
ERROR at line 1:
ORA-08182: operation not supported while in Flashback mode
11:05:59 TYGER@ORCL>exec dbms_flashback.disable;
PL/SQL procedure successfully completed.
11:06:18 TYGER@ORCL>select * from fb_query1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
11:06:30 TYGE