闪回数据归档：历史数据和元数据
Oracle 11g在FBDA中存储数据时没有“重新设计车轮”，每个启用FBDA的表使用三个简单的表结构，每个都以<源表所有者>.SYS_FBA_<目的>_格式命名，如表1所示。这些表中的数据可以直接查询，对于那些想一探Oracle 11g是如何管理FBDA基础结构的人来说非使用这些表不可。
表1.闪回数据归档：历史数据存储
为了便于解说，我首先在HR.APPLICANTS表上使用DML语句对其做了一个改变，然后我清洗了数据库缓冲区，并使用ALTER SYSTEM SET UNDO_TABLESPACE=... SCOPE=BOTH; 命令将UNDO过程切换到一个不同的表空间上，请看下面的代码：
-- 对HR.APPLICANTS表做一些改变以便在对应的FBDA对象中自动产生"delta"
记录对最接近1000美元的申请者申请的薪水进行四舍五入
UPDATE hr.applicants
SET salary_desired = ROUND(salary_desired, -3)
WHERE MOD(applicant_id,5) = 0;
COMMIT;
-- 对最接近100美元的申请者申请的薪水进行四舍五入
UPDATE hr.applicants
SET salary_desired = ROUND(salary_desired, -2)
WHERE MOD(applicant_id,5) <> 0
AND MOD(applicant_id,3) = 0;
COMMIT;
-- 现在清空数据库缓冲区和当前UNDO表空间的内容
确保闪回版本查询从闪回数据归档中返回HR.APPLICANTS表的内容
DROP TABLESPACE smallundo INCLUDING CONTENTS AND DATAFILES;
CREATE UNDO TABLESPACE smallundo
DATAFILE '/u01/app/oracle/oradata/ORCL/tsp_smallundo01.dbf'
SIZE 2M;
ALTER SYSTEM CHECKPOINT;
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM SET UNDO_TABLESPACE = 'SMALLUNDO' SCOPE=BOTH;
TTITLE 'Sample Data from Flashback Data Archives|(From SYS_FBA_HIST_73218)'
COL applicant_id FORMAT 99999 HEADING 'Appl|ID'
COL endscn FORMAT 9999999 HEADING 'Ending|SCN'
COL last_name FORMAT A20 HEADING 'Last Name'
COL gender FORMAT A2 HEADING 'G|N|D|R'
COL salary_desired FORMAT 999999.99 HEADING 'Salary|Desired'
COL job_desired FORMAT A10 HEADING 'Job|Desired'
SELECT
applicant_id
,endscn
,last_name
,gender
,salary_desired
,job_desired
FROM HR.SYS_FBA_HIST_73218
WHERE applicant_id < 10
ORDER BY applicant_id, endscn;
TTITLE OFF
TTITLE 'Sample Data from HR.APPLICANTS|(Between Time Periods)'
COL applicant_id FORMAT 99999 HEADING 'Appl|ID'
COL versions_endscn FORMAT 9999999 HEADING 'Ending|SCN'
COL last_name FORMAT A20 HEADING 'Last Name'
COL gender FORMAT A2 HEADING 'G|N|D|R'
COL salary_desired FORMAT 999999.99 HEADING 'Salary|Desired'
COL job_desired FORMAT A10 HEADING 'Job|Desired'
SELECT
applicant_id
,VERSIONS_ENDSCN
,last_name
,gender
,salary_desired
,job_desired
FROM hr.applicants
VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2008-12-04 04:28','yyyy-mm-dd hh24:mi')
AND TO_TIMESTAMP('2008-12-04 04:55','yyyy-mm-dd hh24:mi')
WHERE APPLICANT_ID < 10
ORDER BY applicant_id, versions_endscn;
TTITLE OFF
|
当UNDO表空间成功切换后，我查询SYS_FBA_HIST_73218表获取最近的UNDO事务，结果如下：
Sample Data from Flashback Data Archives
(From SYS_FBA_HIST_73218)
G
N
Appl Ending D Salary Job
ID SCN Last Name R Desired Desired
------ -------- -------------------- -- ---------- ----------
3 1203058 Brown M 70113.04 IT_CNTR2
6 1203058 Chandler M 55511.77 IT_CNTR1
9 1203058 Chestnut M 73042.53 IT_CNTR3
|
直接从表本身查询，对比如下：
Sample Data from HR.APPLICANTS
(Between Time Periods)
G
N
Appl Ending D Salary Job
ID SCN Last Name R Desired Desired
------ -------- -------------------- -- ---------- ----------
1 Aniston M 88017.94 IT_CNTR2
2 Niven M 82553.39 IT_CNTR1
3 1203058 Brown M 70113.04 IT_CNTR2
3 Brown M 70100.00 IT_CNTR2
4 Murdock M 70389.16 IT_CNTR2
5 1202273 Bedelia M 38720.86 IT_CNTR3
5 Bedelia M 39000.00 IT_CNTR3
6 1203058 Chandler M 55511.77 IT_CNTR1
6 Chandler M 55500.00 IT_CNTR1
7 Lerner M 80587.46 IT_CNTR2
8 Robinson M 49516.37 IT_CNTR3
9 1203058 Chestnut M 73042.53 IT_CNTR3
9 Chestnut M 73000.00 IT_CNTR3
|
FBDA元数据：Oracle 11gR1提供了几个关于FBDA元数据的数据字典视图，包括哪个表空间支持可扩展的历史数据存储，以及FBDA中保留了哪个表：
表2.闪回数据归档：元数据
