--- 说明闪回数据库
--- 使用闪回表将表内容还原到过去的特定时间点
--- 从删除表中进行恢复
--- 使用闪回查询查看截止到任一时间点的数据库内容
--- 使用闪回版本查询查看某一行在一段时间内的各个版本
--- 使用闪回事务查询查看事务处理历史记录或行
优点：
闪回技术由于只能处理更改数据，所以从根本上改变了恢复技术。使用这个技术时，从错误中恢复花费的时间等于制造错误所花费的时间。当闪回技术使用时，它与介质恢复相比，在易用性、可用性和还原时间方面有明显的优势。
闪回数据库使用闪回日志执行闪回。闪回删除使用回收站。其他所有功能都使用还原数据。
闪回时间浏览
闪回技术提供的功能可用于查询方案对象的过去版本、查询历史记录数据以及执行更改分析。每个事务处理在逻辑上都会生成新版本数据库。使用闪回技术，可通过浏览这些版本来查找错误以及原因。
· 闪回查询：查询特定时间点的所有数据。
· 闪回版本查询：查看两个时间之间行的所有版本已经更改了行的事务处理。
· 闪回事务处理查询：查看事务处理做的所有更改。
使用闪回查询功能时，可以对自特定时间起的数据库执行查询。通过使用select语句的 as of 子句，可指定要查看其数据的时间戳。这有助于分析数据差异。
实验一：闪回查询
实验一：闪回查询：as of timestamp
sys@orcl>conn tyger/tyger
connected.
tyger@orcl>create table fb_query as select * from scott.dept;
table created.
tyger@orcl>select * from fb_query;
deptno dname loc
---------- -------------- -------------
10 accounting new york
20 research dallas
30 sales chicago
40 operations boston
tyger@orcl>set time on;
09:51:36 tyger@orcl>delete fb_query where deptno=10;
1 row deleted.
09:51:53 tyger@orcl>commit;
commit complete.
09:51:57 tyger@orcl>select * from fb_query;
deptno dname loc
---------- -------------- -------------
20 research dallas
30 sales chicago
40 operations boston
09:52:06 tyger@orcl>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
实验二：闪回查询应用
10:25:04 tyger@orcl>drop table fb_tyger purge;
table dropped.
10:25:10 tyger@orcl>create table fb_tyger as select * from scott.dept;
table created.
10:25:33 tyger@orcl>select * from fb_tyger;
deptno dname loc
---------- -------------- -------------
10 accounting new york
20 research dallas
30 sales chicago
40 operations boston
10:25:44 tyger@orcl>select sysdate from dual;
sysdate
---------
14-mar-14
10:26:02 tyger@orcl>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
session altered.
10:26:30 tyger@orcl>select sysdate from dual;
sysdate
-------------------
2014-03-14 10:26:38
10:26:38 tyger@orcl>update fb_tyger set dname='';
4 rows updated.
10:26:51 tyger@orcl>commit;
commit complete.
10:26:54 tyger@orcl>select * from fb_tyger;
deptno dname loc
---------- -------------- -------------
10 new york
20 dallas
30 chicago
40 boston
10:27:12 tyger@orcl>select * from fb_tyger as of timestamp to_timestamp('2014-03-14 10:26:38','yyyy-mm-dd hh24:mi:ss');
deptno dname loc
---------- -------------- -------------
10 accounting new york
20 research dallas
30 sales chicago
40 operations boston
此处遇到错误：
error at line 1:
ora-01466: unable to read data - table definition has changed
参考文档：
10:29:21 tyger@orcl>select * from fb_tyger as of timestamp sysdate-3/1440;
deptno dname loc
---------- -------------- -------------
10 accounting new york
20 research dallas
30 sales chicago
40 operations boston
10:29:35 tyger@orcl>select * from fb_tyger;
deptno dname loc
---------- -------------- -------------
10 new york
20 dallas
30 chicago
40 boston
10:46:22 tyger@orcl>set time off
tyger@orcl>update fb_tyger t
2 set dname =
3 (select dname from fb_tyger as of timestamp
4 to_timestamp('2014-03-14 10:26:38','yyyy-mm-dd hh24:mi:ss')
5 where t.deptno=fb_tyger.deptno);
4 rows updated.
tyger@orcl>commit;
commit complete.
tyger@orcl>select * from fb_tyger;
deptno dname loc
---------- -------------- -------------
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
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 tyger@orcl>select * from fb_query2;
deptno dname loc
---------- -------------- -------------
10 new york
20 dallas
30 chicago
40 boston
11:06:37 tyger@orcl>update fb_query1 set dname='' where deptno=10;
1 row updated.
11:07:10 tyger@orcl>select * from fb_query1;
deptno dname loc
---------- -------------- -------------
10
20 research
30 sales
40 operations
// sys 用户不允许使用dbms_flashback 包
11:07:20 tyger@orcl>conn / as sysdba
connected.
11:07:35 sys@orcl>set time off
sys@orcl>exec dbms_flashback.enable_at_time(sysdate-5/1440);
begin dbms_flashback.enable_at_time(sysdate-5/1440); end;
*
error at line 1:
ora-08185: flashback not supported for user sys
ora-06512: at sys.dbms_flashback, line 3
ora-06512: at line 1
实验二：闪回版本
---通过闪回版本可审计表行，检索影响行的事务处理的有关信息。然后可使用返回的事务处理标识符来执行事务处理挖掘(通过使用logminer)或执行闪回版本查询。