---SYS_FBA_HIST_36945采用的是range分区，endscn作为partition key
set linesize 100
select owner,table_name,PARTITIONING_TYPE,SUBPARTITIONING_TYPE from dba_part_tables where table_name='SYS_FBA_HIST_36945';
OWNER TABLE_NAME PARTITION SUBPARTIT
------------------------------ ------------------------------ --------- ---------
SCOTT SYS_FBA_HIST_36945 RANGE NONE
SCOTT@tstdb1-SQL> col column_name format a20
SCOTT@tstdb1-SQL> select name,column_name from dba_part_key_columns where;
NAME COLUMN_NAME
------------------------------ --------------------
SYS_FBA_HIST_36945 ENDSCN
---存放历史数据的分区启用了compress for oltp方式的压缩
set long 2000 linesize 150
col TABLE_OWNER format a20
col TABLE_NAME format a25
col partition_name format a15
col high_value format a40
select TABLE_OWNER,TABLE_name,PARTITION_NAME,compression,compress_for,COMPOSITE,HIGH_VALUE from dba_tab_partitions where table_name='SYS_FBA_HIST_36945';
TABLE_OWNER TABLE_NAME PARTITION_NAME COMPRESS COMPRESS_FOR COM HIGH_VALUE
-------------------- ------------------------- --------------- -------- ------------ --- ----------------------------------------
SCOTT SYS_FBA_HIST_36945 HIGH_PART ENABLED OLTP NO MAXVALUE
注：测试环境是11.2.0.3，如果是11.2.0.4及以后版本，因为引入了optimize data的功能，默认情况下创建的archive table是不压缩的，除非在create flashback archive时指定了optimize data，在11.2.0.4及以后可以在创建flashback archive时加入"optimize data"选项：create flashback archive fba0516_2 tablespace tbs0516_1 optimize data retention 1 day;
---SYS_MFBA_NHIST_36945、SYS_FBA_TCRV_36945表在RID字段上建有索引，数据量大的时候提高基于rowid的检索效率
SCOTT@tstdb1-SQL> select index_name,column_name from dba_ind_columns where table_name='SYS_FBA_HIST_36945';
no rows selected
SCOTT@tstdb1-SQL> select index_name,column_name from dba_ind_columns where table_name='SYS_MFBA_NHIST_36945';
INDEX_NAME COLUMN_NAME
------------------------------ --------------------
SYS_MFBA_NHIST_36945_IDX RID
SCOTT@tstdb1-SQL> select index_name,column_name from dba_ind_columns where table_name='SYS_FBA_TCRV_36945';
INDEX_NAME COLUMN_NAME
------------------------------ --------------------
SYS_FBA_TCRV_IDX_36945 RID
SCOTT@tstdb1-SQL> select index_name,column_name from dba_ind_columns where table_name='SYS_FBA_DDL_COLMAP_36945';
no rows selected
/////////////Part 3. SYS_FBA_表是如何被使用的/////////////
1、如果SYS_FBA_已经由FBDA进程创建，在flashback query的时候就会去访问SYS_FBA系列表，从执行计划中很容易看出来
explain plan for select * from t0516_5 as of scn 12723378743688;
SQL> set pagesize 100 linesize 150
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2508115242
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 48 | 21 (10)| 00:00:01 | | |
| 1 | VIEW | | 3 | 48 | 21 (10)| 00:00:01 | | |
| 2 | UNION-ALL | | | | | | | |
| 3 | PARTITION RANGE SINGLE| | 2 | 54 | 14 (0)| 00:00:01 | 1 | 1 |
|* 4 | TABLE ACCESS FULL | SYS_FBA_HIST_36945 | 2 | 54 | 14 (0)| 00:00:01 | 1 | 1 |
|* 5 | FILTER | | | | | | | |
| 6 | MERGE JOIN OUTER | | 1 | 40 | 7 (29)| 00:00:01 | | |
| 7 | SORT JOIN | | 1 | 7 | 3 (34)| 00:00:01 | | |
|* 8 | TABLE ACCESS FULL | T0516_5 | 1 | 7 | 2 (0)| 00:00:01 | | |
|* 9 | SORT JOIN | | 2 | 66 | 4 (25)| 00:00:01 | | |
|* 10 | TABLE ACCESS FULL | SYS_FBA_TCRV_36945 | 2 | 66 | 3 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------