閃回資料歸檔：oracle total recall ORACLE全面回憶功能。
ORACLE 11G提供的新功能。
通過這一功能ORACLE将UNDO資料進行歸檔，進而提供全面的曆史資料查詢。
類似歸檔日志，11G新增的背景程序FBDA（flashback data archiver process)用于對閃回資料進行歸檔寫出。
ORACLE可以指定閃回歸檔資料儲存時間，并可以通過内部分區和壓縮算法減少空間使用。
閃回資料歸檔需要獨立的存儲，使用此特性前需要建立獨立的ASSM表空間。
DDL不允許使用在被FBDA跟蹤的table上（add column,rename和grant可以）。
開始實驗：
1.建立FBDA程序所需表空間-FLASH BACK DATA ARCHIVE-閃回資料歸檔
21:44:32 SQL> create tablespace bys_flashback datafile '/u01/app/oracle/oradata/bys001/bys_flashback.dbf' size 10m autoextend off;
Tablespace created.
21:45:35 SQL> show user
USER is "SYS"
Default的FBDA需要用sysdba登陸才能建立, 并且隻能有一個Default的FBDA
21:45:39 SQL> create flashback archive default fbda1 tablespace bys_flashback retention 7 day;
Flashback archive created.
21:46:36 SQL> create flashback archive fbda2 tablespace bys_flashback quota 7m retention 10 day;
Flashback archive created.
2.建立表并指定閃回歸檔屬性，記錄DML操作前後SCN
21:47:52 SQL> conn bys/bys
Connected.
21:49:54 SQL> create table test6(abc varchar2(9)) flashback archive;
Table created.
對已經存在的表改變或取消表的閃回歸檔可以使用
alter table test6 flashback archive fbda2;-- 網上查的，建立的閃回歸檔表名？
alter table test6 no flashback archive;
插入資料，省略示範。
21:51:27 SQL> select * from test6;
ABC
---------
1
2
3
21:51:32 SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1372223
21:51:44 SQL> delete from test6 where abc=3;
1 row deleted.
21:52:12 SQL> commit;
Commit complete.
21:52:14 SQL> host 這裡的ALERT日志是從TRACE目錄下源ALERT日志上做一個軟連結，友善檢視日志。
aa.t back1.sh Desktop
alert_bys001.log back2-20130623-1144.log fullback.sh
archback.sh back2.sh rmanlog
back0.sh cumulative
3.從alert日志中檢視閃回歸檔表空間的建立及FBDA程序的啟動
[[email protected] ~]$ tail alert_bys001.log
SUPLOG: unique = OFF, foreign key = OFF, all column = OFF
SUPLOG: procedural replication = OFF
Completed: alter database add supplemental log data
Sun Jun 23 21:45:33 2013
create tablespace bys_flashback datafile '/u01/app/oracle/oradata/bys001/bys_flashback.dbf' size 10m autoextend off
Completed: create tablespace bys_flashback datafile '/u01/app/oracle/oradata/bys001/bys_flashback.dbf' size 10m autoextend off
Sun Jun 23 21:46:36 2013
Starting background process FBDA
Sun Jun 23 21:46:36 2013
FBDA started with pid=33, OS id=3961
[[email protected] ~]$ exit
exit
21:54:08 SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
21:56:08 SQL> col name for a40
21:56:20 SQL> select file#,name from v$datafile where name like '%test%';
FILE# NAME
---------- ----------------------------------------
7 /u01/app/oracle/oradata/bys001/test1_undo.dbf
未使用此句：
drop tablespace undotbs1 including contents and datafiles;
4.使用一個空間較小的UNDO表空間，友善實驗。
21:56:21 SQL> alter system set undo_tablespace='test1_undo';
System altered.
21:58:12 SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string test1_undo
清除緩沖區資料
21:58:19 SQL> alter system flush buffer_cache;
System altered.
22:00:55 SQL> select * from test6;
ABC
---------
1
2
22:02:59 SQL> set autotrace on explain
5.通過執行計劃可以看到查詢來自SYS_FBA_TCRV_75320，這是屬于閃回歸檔表空間用于記錄閃回資料。
22:03:14 SQL> select * from test6 as of scn 1372223;
ABC
---------
3
1
2
Execution Plan
----------------------------------------------------------
Plan hash value: 2569713660
--------------------------------------------------------------------------------
-------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
PU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
-------------------------------
| 0 | SELECT STATEMENT | | 5 | 30 | 9 (
12)| 00:00:01 | | |
| 1 | VIEW | | 5 | 30 | 9 (
12)| 00:00:01 | | |
| 2 | UNION-ALL | | | |
| | | |
| 3 | PARTITION RANGE SINGLE| | 1 | 32 | 3
(0)| 00:00:01 | 1 | 1 |
|* 4 | TABLE ACCESS FULL | SYS_FBA_HIST_75320 | 1 | 32 | 3
(0)| 00:00:01 | 1 | 1 |
|* 5 | FILTER | | | |
| | | |
|* 6 | HASH JOIN OUTER | | 4 | 8184 | 6 (
17)| 00:00:01 | | |
|* 7 | TABLE ACCESS FULL | TEST6 | 4 | 72 | 2
(0)| 00:00:01 | | |
|* 8 | TABLE ACCESS FULL | SYS_FBA_TCRV_75320 | 3 | 6084 | 3
(0)| 00:00:01 | | |
--------------------------------------------------------------------------------
-------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("ENDSCN">1372223 AND "ENDSCN"<=1372730 AND ("STARTSCN" IS NULL OR
"STARTSCN"<=1372223))
5 - filter("STARTSCN"<=1372223 OR "STARTSCN" IS NULL)
6 - access("T".ROWID=CHARTOROWID("RID"(+)))
7 - filter("T"."VERSIONS_STARTSCN" IS NULL)
8 - filter(("ENDSCN"(+) IS NULL OR "ENDSCN"(+)>1372730) AND ("STARTSCN"(+) IS
NULL OR
"STARTSCN"(+)<1372730))
Note
-----
- dynamic sampling used for this statement (level=2)