ID C3
---------------- ---
1 AAA
3 DDD
---继续跟踪SYS_FBA_表的变化情况，最多等待5分钟能观察到下列表中的记录变化情况
SCOTT@tstdb1-SQL> select * from sys.SYS_MFBA_NHIST_36945;
no rows selected
***SYS_FBA_HIST_36945保存的是before-image，scn: 12723378739723~12723378743689范围内表里存在c3='CCC'的记录，scn: 12723378739723~12723378743708范围内表里存在C3='BBB'的记录，scn：12723378743708时刻C3='BBB'的记录被XID=000A001A00015D0B的Transaction delete掉，这些记录现在都已经不在表中了
SCOTT@tstdb1-SQL> select * from SYS_FBA_HIST_36945;
RID STARTSCN ENDSCN XID O ID C3
-------------------- ---------------- ---------------- ---------------- - ---------------- ---
AAAJBRAAEAAAWjJAAB 12723378743708 12723378743708 000A001A00015D0B D 2 BBB
AAAJBRAAEAAAWjJAAB 12723378739723 12723378743708 000A000500015C8E I 2 BBB
AAAJBRAAEAAAWjJAAC 12723378739723 12723378743689 000A000500015C8E I 3 CCC
***映射关系维持不变
SCOTT@tstdb1-SQL> select * from SYS_FBA_DDL_COLMAP_36945;
STARTSCN ENDSCN XID O COLUMN_NAME TYPE HISTORICAL_COLUMN_NA
---------------- ---------------- ---------------- - -------------------- -------------------- --------------------
12723378739636 ID NUMBER ID
12723378739636 12723378742951 C3 VARCHAR2(3) C2
12723378742951 C3 VARCHAR2(3) C3
***SYS_FBA_TCRV_36945与flashback version query的结果及其相似，记录了源表的操作历史，结合SYS_FBA_HIST_36945能够准确的找到过去某个scn下的before image
SCOTT@tstdb1-SQL> select * from SYS_FBA_TCRV_36945;
RID STARTSCN ENDSCN XID O
-------------------- ---------------- ---------------- ---------------- -
AAAJBRAAEAAAWjJAAA 12723378739723 000A000500015C8E I
AAAJBRAAEAAAWjJAAB 12723378739723 12723378743708 000A000500015C8E I
AAAJBRAAEAAAWjJAAC 12723378739723 12723378743689 000A000500015C8E I
AAAJBRAAEAAAWjJAAC 12723378743689 000A000D00015C87 U
执行select * from t0516_5 as of scn 12723378743688语句时寻找过程大致如下：rowid='AAAJBRAAEAAAWjJAAA'的行12723378743688>=startscn，endscn为空，表示这行从表里取现值id=1、C3='AAA'，无需访问archive table；
rowid='AAAJBRAAEAAAWjJAAB'的行endscn>12723378743688>=startscn，表示这行在scn:12723378743688时刻不在表里，需要访问archive table(SYS_FBA_HIST_36945)里rowid='AAAJBRAAEAAAWjJAAB' and XID=000A000500015C8E对应行获取before-image:id=2、C3='BBB'
rowid='AAAJBRAAEAAAWjJAAC'有两行，根据scn：12723378743688对应到startscn=12723378739723 and endscn=12723378743689这一行，然后去SYS_FBA_HIST_36945找到rowid='AAAJBRAAEAAAWjJAAC' and xid='000A000500015C8E'返回before-image:id=3、C3='CCC'
SCOTT@tstdb1-SQL> select * from t0516_5 as of scn 12723378743688;
ID C3
---------- ---
2 BBB
3 CCC
1 AAA
/////////////Part 2. SYS_FBA_表结构说明////////////////
select owner,table_name,partitioned from dba_tables where table_name like '%36945';
OWNER TABLE_NAME PAR
------------------------------ ------------------------------ ---
SCOTT SYS_FBA_HIST_36945 YES
SYS SYS_MFBA_NHIST_36945 NO
SCOTT SYS_FBA_TCRV_36945 NO
SCOTT SYS_FBA_DDL_COLMAP_36945 NO
SYS_FBA_HIST_121239表是分区表，before-image保留在这张表里，为何提高访问性能oracle把它建成了分区表，初始只有一个分区