閃回之 Flashback Query （dml表、過程、函式、包等）、Flashback version Query

Flashback Query 背景：
Flashback 是 ORACLE 自 9i 就開始提供的一項特性，在 9i 中利用oracle 查
詢多版本一致的特點，實現從回滾段中讀取表一定時間內操作過的資料，可用來
進行資料比對，或者修正意外提交造成的錯誤資料，該項特性也被稱為 Flashback
Query。

Flashback Query 種類：
Flashback Query 分 Flashback Query,Flashback Version Query， Flashback Transaction Query 三種。

flashback query 限制：
1. lashback query 對 v$tables,x$tables 等動態效能檢視無效
2. 對於dba_*,all_*,user_*等資料字典是有效的

一 、 Flashback Query As of timestamp 的示例：

SQL> alter session set nls_date_format='YYYY-MM-DD hh24:mi:ss';
SQL> create table query as select * from user_objects;
SQL> select count(*) from query;
SQL> select sysdate from dual;
SYSDATE
-------------------
2015-03-13 16:29:13
SQL> delete from query;
SQL> commit;
SQL> select * from query;
no rows selected
--檢視刪除之前的狀態：假設當前距離刪除資料已經有 5 分鐘左右的話：
SQL> select * from query as of timestamp sysdate-5/1440;
或者：
--知道誤操作的準確時間，檢視誤操作之前的狀態
SQL>select * from query as of timestamp to_timestamp('2015-03-13 16:29:13','YYYY-MM-DD hh24:mi:ss');
用 Flashback Query 恢復之前的資料：
SQL>Insert into query select * from query as of timestamp to_timestamp('2015-03-13 16:29:13','YYYY-MM-DD hh24:mi:ss');
14 rows created.
SQL> COMMIT;
SQL> select * from query;
14 rows selected.

注意 ： as of timestamp 的確非常易用，但是在某些情況下，
我們建議使用 as of scn 的方式執行 flashback query，比如需要對多個相互有主外
鍵約束的表進行恢復時，如果使用 as of timestamp 的方式，可能會由於時間點不
統一的緣故造成資料選擇或插入失敗，透過 scn 方式則能夠確保記錄的約束一致性。

補充：檢視 SCN 和 timestamp 之間的對應關係：
select timestamp_to_scn(TO_TIMESTAMP_TZ('2015-03-13 16:52:30','YYYY-MM-DD HH24:MI:SS')) to_scn from dual;

二、 Flashback Query As of scn 的示例：

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
3473243

SQL> delete from query;
14 rows deleted.
SQL> commit;
--檢視刪除之前的狀態：
SQL> select * from query as of scn 3473243;
14 rows selected.
用 Flashback Query 恢復之前的資料：
SQL> insert into query select * from query as of scn 3473243;
SQL> commit;
SQL> select count(*) from query;
COUNT(*)
----------
14

補充：檢視 SCN 和 timestamp 之間的對應關係：
-- timestamp 轉 scn 
select timestamp_to_scn(TO_TIMESTAMP_TZ('2015-03-13 17:26:42','YYYY-MM-DD HH24:MI:SS')) to_scn from dual;
-- scn 轉 timestamp
SQL> select scn_to_timestamp(3474603) scn from dual;

三、 Flashback Query 函式，儲存過程，包，觸發器等物件:

背景
Flashback Drop 可以閃回與表相關聯的物件， 如果是其他的物件，比如function，procedure，trigger 等。 這時候，就需要使用到 ALL_SOURCE 表來進行 Flashback Query。

檢視 dba_source 的所有 type
SQL> select type from dba_source group by type;

TYPE
------------
PACKAGE
PACKAGE BODY
TYPE BODY
FUNCTION
JAVA SOURCE
PROCEDURE
LIBRARY
TRIGGER
TYPE

9 rows selected.

恢復操作流程：
--建立函式：
create or replace function fadd(pEndNumber int) return int
as
i int;
result int;
begin
i:=0;
result:=0;
while i<=pEndNumber loop result:=result+i;i:=i+1; end loop;
return result;
end;
/

--記錄時間
SQL> select sysdate from dual;
SYSDATE
-------------------
2015-03-13 17:26:42
--查詢函式：
SQL> set serveroutput on;
SQL> select fadd(100) from dual;

FADD(100)
----------
5050
--查詢 dba_source 表：
SQL> select text from dba_source where name='FADD' order by line;
TEXT
--------------------------------------------------------------------------------
10 rows selected.

drop 函式，在查詢，記錄不存在
SQL> drop function fadd;
Function dropped.
SQL> select text from dba_source where name='FADD' order by line;
no rows selected

使用我們的 Flashback Query 查詢：
SQL> 
select text from dba_source as of timestamp to_timestamp('2015-03-13 17:26:42','yyyy-mm-dd hh24:mi:ss') where name='FADD' order by line;

TEXT
--------------------------------------------------------------------------------
function fadd(pEndNumber int) return int
as
i int;
result int;
begin
i:=0;
result:=0;
while i<=pEndNumber loop result:=result+i;i:=i+1; end loop;
return result;
end;

10 rows selected.

text輸出結果，重新執行一下就恢復回來了，其他的物件類推，不再演示。

四、 Flashback version Query：

相對於 Flashback Query 只能看到某一點的物件狀態， Oracle 10g 引入的
Flashback Version Query 可以看到過去某個時間段內，記錄是如何發生變化的。
根據這個歷史，DBA 就可以快速的判斷資料是在什麼時點發生了錯誤，進而恢
復到之前的狀態。

先看一個偽列 ORA_ROWSCN. 所謂的偽列，就是假的，不存在的資料列，
使用者建立表時雖然沒有指定，但是 Oracle 為了維護而新增的一些內部欄位，這
些欄位可以像普通檔案那樣的使用。ORA_ROWSCN 是 Oracle 10g 新增的，暫且把它看作是記錄 最後一次 被修
改時的 SCN。 Flashback Version Query 就是透過這個偽列來跟蹤出記錄的變化歷史。

實驗流程：
SQL> create table andy (id int);
Table created.

SQL> insert into andy values(1);
1 row created.

SQL> insert into andy values(2);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from andy;
ID
----------
1
2

SQL> select ora_rowscn, id from andy;
ORA_ROWSCN ID
---------- ----------
3476348 1
3476348 2

-- 檢視歷史資訊
SQL>
Select versions_xid,versions_startscn,versions_endscn,
DECODE(versions_operation,'I','Insert','U','Update','D','Delete', 'Original')
"Operation", id from andy versions between scn minvalue and maxvalue;

VERSIONS_XID VERSIONS_STARTSCN VERSIONS_ENDSCN Operatio ID
---------------- ----------------- --------------- -------- ----------
04000100830A0000 3476348 Insert 2
04000100830A0000 3476348 Insert 1
或者

ORA_ROWSCN 預設是資料塊級別的，也就是一個資料塊內的所有記錄都
是一個 ORA_ROWSCN，資料塊內任意一條記錄被修改，這個資料庫塊內的所
有記錄的 ORA_ROWSCN 都會同時改變。

SQL> delete from andy where id>3;
2 rows deleted.

SQL> 
Select versions_xid,versions_startscn,versions_endscn,
DECODE(versions_operation,'I','Insert','U','Update','D','Delete', 'Original')
"Operation", id from andy versions between scn minvalue and maxvalue;

VERSIONS_XID VERSIONS_STARTSCN VERSIONS_ENDSCN Operatio ID
---------------- ----------------- --------------- -------- ----------
01000D00910A0000 3477149 Insert 5
01000D00910A0000 3477149 Insert 4
02000600000B0000 3477111 Insert 3
Original 1
Original 2

SQL> select * from andy;
ID
----------
1
2
3

SQL> commit;
Commit complete.

SQL> 
Select versions_xid,versions_startscn,versions_endscn,
DECODE(versions_operation,'I','Insert','U','Update','D','Delete', 'Original')
"Operation", id from andy versions between scn minvalue and maxvalue;

VERSIONS_XID VERSIONS_STARTSCN VERSIONS_ENDSCN Operatio ID
---------------- ----------------- --------------- -------- ----------
06001900EF0A0000 3477710 Delete 5
06001900EF0A0000 3477710 Delete 4
01000D00910A0000 3477149 3477710 Insert 5
01000D00910A0000 3477149 3477710 Insert 4
02000600000B0000 3477111 Insert 3
Original 1
Original 2

7 rows selected.

SQL> select * from andy as of scn 3477710;
ID
----------
1
2
3

SQL> select * from andy as of scn 3477709;
ID
----------
1
2
3
4
5
SQL> insert into andy select * from andy as of scn 3477709;
5 rows created.

SQL> select * from andy;
ID
----------
1
2
3
1
2
3
4
5

8 rows selected.

說明： 可以根據 Flashback version Query 中的歷史scn 找到 Flashback Query 的scn點。