--- 說明閃回數據庫
--- 使用閃回表將表內容還原到過去的特定時間點
--- 從刪除表中進行恢復
--- 使用閃回查詢查看截止到任一時間點的數據庫內容
--- 使用閃回版本查詢查看某一行在一段時間內的各個版本
--- 使用閃回事務查詢查看事務處理歷史記錄或行
優點：
閃回技術由於只能處理更改數據，所以從根本上改變了恢復技術。使用這個技術時，從錯誤中恢復花費的時間等於制造錯誤所花費的時間。當閃回技術使用時，它與介質恢復相比，在易用性、可用性和還原時間方面有明顯的優勢。
閃回數據庫使用閃回日志執行閃回。閃回刪除使用回收站。其他所有功能都使用還原數據。
閃回時間浏覽
閃回技術提供的功能可用於查詢方案對象的過去版本、查詢歷史記錄數據以及執行更改分析。每個事務處理在邏輯上都會生成新版本數據庫。使用閃回技術，可通過浏覽這些版本來查找錯誤以及原因。
· 閃回查詢：查詢特定時間點的所有數據。
· 閃回版本查詢：查看兩個時間之間行的所有版本已經更改了行的事務處理。
· 閃回事務處理查詢：查看事務處理做的所有更改。
使用閃回查詢功能時，可以對自特定時間起的數據庫執行查詢。通過使用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
參考文檔： http://blog.csdn.net/wanghui5767260/article/details/21227101
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<=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 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)或執行閃回版本查詢。
所謂版本(version)指的是每次事務所引起的數據行的變化情況，每次變化就是一個版本，oracle提供了閃回版本查詢，從而可以讓讓我們很清楚地看到數據行的整個變化過程，這裡的變化都是已經提交了的事務引起的變化，沒有提交的事務引起的變化不會顯示，閃回版本查詢利用的是undo表空間裡記錄的undo數據。
使用偽列 獲取一段時間內的版本
偽列：versions_starttime、versions_endtime、versions_startscn、versions_endscn、versions_xid、versions_operation、
versions_startscn versions_starttime
操作時的SCN和時間 如果為空 表示該行在查詢范圍之外創建
versions_endscn versions_endtime
失效時的SCN和時間 如果為空 表示該行被刪除或在查詢范圍內無改動
versions_xid
事務ID
versions_operation
該行被執行的操作 I(insert) D(delete) U(update)
minvalue maxvalue
版本的最大時間值和最小時間值
注意事項：
versions子句不能用於查詢以下特殊表：
· 外部表
· 臨時表
· 固定表(x$開頭的表，也就是oracle內部的表，用於數據字典的基表)，下面sql語句可查詢相關表
select * from v$fixed_table
不能使用versions子句查詢視圖。但是，在視圖定義中可使用versions子句。
· versions子句不能跨DDL命令使用
· 過濾掉段收縮操作過的行
實驗：閃回版本查詢
TYGER@ORCL>create table tyger as select ename,job,sal from scott.emp where rownum<5;
Table created.
TYGER@ORCL>select * from tyger;
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 857
ALLEN SALESMAN 1656
WARD SALESMAN 1306
JONES MANAGER 3031
TYGER@ORCL>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
TYGER@ORCL>select sysdate from dual;
SYSDATE
-------------------
2014-03-14 14:41:46
TYGER@ORCL>update tyger set sal=sal+100 where ename='SMITH';
1 row updated.
TYGER@ORCL>commit;
Commit complete.
TYGER@ORCL>update tyger set sal=sal+100 where ename='SMITH';
1 row updated.
TYGER@ORCL>commit;
Commit complete.
TYGER@ORCL>update tyger set sal=sal+100 where ename='SMITH';
1 row updated.
TYGER@ORCL>commit;
Commit complete.
TYGER@ORCL>select * from tyger;
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 1157
ALLEN SALESMAN 1656
WARD SALESMAN 1306
JONES MANAGER 3031
TYGER@ORCL>col starttime for a30
TYGER@ORCL>l
1 select to_char(versions_starttime,'yyyy-mm-dd hh24:mi:ss') as starttime,
2 versions_xid,ename,job,sal
3 from tyger versions between timestamp to_date('2014-03-14 14:41:46','yyyy-mm-dd hh24:mi:ss')
4* and sysdate where ename='SMITH'
TYGER@ORCL>/
STARTTIME VERSIONS_XID ENAME JOB SAL
------------------------------ ---------------- ---------- --------- ----------
2014-03-14 14:42:32 080016000F020000 SMITH CLERK 1157
2014-03-14 14:42:26 01002C00F1010000 SMITH CLERK 1057
2014-03-14 14:42:17 0600180025020000 SMITH CLERK 957
SMITH CLERK 857
或者
TYGER@ORCL>col versions_starttime for a22
TYGER@ORCL>col versions_endtime for a22
TYGER@ORCL>l
1 select versions_starttime,versions_endtime,versions_xid,versions_operation,ename
2* from tyger versions between timestamp to_timestamp('2014-03-14 14:41:46','yyyy-mm-dd hh24:mi:ss') and maxvalue order by 1
TYGER@ORCL>/
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V ENAME
---------------------- ---------------------- ---------------- - ----------
14-MAR-14 02.42.17 PM 14-MAR-14 02.42.26 PM 0600180025020000 U SMITH
14-MAR-14 02.42.26 PM 14-MAR-14 02.42.32 PM 01002C00F1010000 U SMITH
14-MAR-14 02.42.32 PM 080016000F020000 U SMITH
WARD
ALLEN
JONES
14-MAR-14 02.42.17 PM SMITH
7 rows selected.
實驗三：閃回事務查詢
flashback 的事務查詢時通過查詢flashback_transaction_query視圖來實現的
通過查詢該視圖能夠獲得一些事務執行時的信息，甚至包括UNDO語句。
每個事務都有事務ID以及SCN關聯關系
閃回事務處理查詢是一中診斷工具，可以用來查看在事務處理級對數據庫所做的更改。這樣，可診斷數據庫中的問題並對事務處理執行分析和審計。
可以使用FLASHBACK_TRANSACTION_QUERY視圖來確定所有必要的SQL語句，這些語句可用來還原特定事務處理或特定時間段內所做的修改。
· 在數據庫中，DDL操作只是對數據字典所做的一系列空間管理操作和更改。通過執行DDL對事務處理執行閃回事務處理查詢時，會顯示對數據字典所做的更改。
· 當閃回事務處理查詢涉及到已從數據庫中刪除的表時，就不會反映表名稱。而是使用對象編號。
· 如果閃回了執行事務處理的用戶，則該事務處理的閃回事務處理查詢只顯示相應的用戶ID，而不是用戶名。
TYGER@ORCL>conn / as sysdba
Connected.
SYS@ORCL>
SYS@ORCL>
SYS@ORCL>select undo_sql from flashback_transaction_query where xid='080016000F020000';
UNDO_SQL
--------------------------------------------------------------------------------
update "TYGER"."TYGER" set "SAL" = '1057' where ROWID = 'AAANQ3AAGAAAAYMAAA';
SYS@ORCL>grant select any transaction to tyger;
Grant succeeded.
SYS@ORCL>conn tyger/tyger
Connected.
TYGER@ORCL>select undo_sql from flashback_transaction_query where xid='080016000F020000';
UNDO_SQL
--------------------------------------------------------------------------------
update "TYGER"."TYGER" set "SAL" = '1057' where ROWID = 'AAANQ3AAGAAAAYMAAA'; //undo語句
