本文介绍Oracle Database 12c中关于数据生命周期管理多个新特性中相对最简单的一个，数据库内归档（In-Database Archiving）。使用的测试表是上一篇介绍数据时间有效期管理中使用的TV表（包括表结构和测试数据），如果你还没有看过上一篇文章，可以先阅读【Oracle Database 12c New Feature】ILM – Temporal Validity。
相比起数据时间有效期管理而言，数据库内归档非常简单，只有一个开关，对于一条数据，要不就是活跃的允许显示，要不就是归档掉不显示，这是由数据库管理员来人工操作的。
在设置数据库内归档之前，必须要在表级别启用该特性。如上一篇文章提到的，In-Database Archiving支持多租户架构，可以在PDB中使用。
SQL> alter table TV row archival;
Table altered.
Oracle仍然是使用隐藏列来实现这个功能的，在启用该特性以后，会自动在表上增加ORA_ARCHIVE_STATE字段，这是一个VARCHAR2(4000)的字段。
SQL> select COLUMN_NAME,DATA_TYPE,HIDDEN_COLUMN FROM USER_TAB_COLS WHERE TABLE_NAME='TV';
COLUMN_NAME DATA_TYPE HID
-------------------- -------------------- ---
ORA_ARCHIVE_STATE VARCHAR2 YES
SYS_NC00005$ RAW YES
VALID_TIME_END DATE YES
VALID_TIME_START DATE YES
INSERT_TIME DATE NO
VALID_TIME NUMBER YES
6 rows selected.
先检查一下TV表中的数据分布，一共有9个不同的时间段，前面5个都只有1条记录，后面4个则有大量测试记录。
SQL> select INSERT_TIME,count(*) from TV group by INSERT_TIME order by 1;
INSERT_TIME COUNT(*)
----------------- ----------
20130811 09:04:30 1
20130811 09:08:27 1
20130811 09:22:30 1
20130811 09:39:40 1
20130811 09:45:22 1
20130811 09:50:44 19368
20130811 09:50:46 19368
20130811 09:50:47 19368
20130811 09:50:48 19368
9 rows selected.
尝试将所有20130811 09:50之后的记录全部设置为归档模式。直接使用UPDATE语句将ORA_ARCHIVE_STATE字段更新为任意非0的字符，0表示该记录是活跃的，任何非0字符都表示该记录被归档。
SQL> UPDATE TV SET ORA_ARCHIVE_STATE = '20' WHERE INSERT_TIME>to_date('20130811 09:50','YYYYMMDD HH24:MI');
77472 rows updated.
再次执行相同的查询语句，可以看到只存在活跃的5条记录了。
SQL> select INSERT_TIME,count(*) from TV group by INSERT_TIME order by 1;
INSERT_TIME COUNT(*)
----------------- ----------
20130811 09:04:30 1
20130811 09:08:27 1
20130811 09:22:30 1
20130811 09:39:40 1
20130811 09:45:22 1
5 rows selected.
可以在会话级别设置即使是记录被归档，也仍然显示出来。
SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;
Session altered.
SQL> select INSERT_TIME,count(*) from TV group by INSERT_TIME order by 1;
INSERT_TIME COUNT(*)
----------------- ----------
20130811 09:04:30 1
20130811 09:08:27 1
20130811 09:22:30 1
20130811 09:39:40 1
20130811 09:45:22 1
20130811 09:50:44 19368
20130811 09:50:46 19368
20130811 09:50:47 19368
20130811 09:50:48 19368
9 rows selected.
检查ORA_ARCHIVE_STATE值，可以看到所有活跃数据的ORA_ARCHIVE_STATE字段值均为0，这也是在表级别启用数据库内归档以后的默认值。
SQL> select ORA_ARCHIVE_STATE,INSERT_TIME,count(*) from TV group by ORA_ARCHIVE_STATE,INSERT_TIME order by 2;
ORA_ARCHIVE_STATE INSERT_TIME COUNT(*)
-------------------- ----------------- ----------
0 20130811 09:04:30 1
0 20130811 09:08:27 1
0 20130811 09:22:30 1
0 20130811 09:39:40 1
0 20130811 09:45:22 1
20 20130811 09:50:44 19368
20 20130811 09:50:46 19368
20 20130811 09:50:47 19368
20 20130811 09:50:48 19368
9 rows selected.
将其中的一些记录的ORA_ARCHIVE_STATE字段更新为另外的非0字符。
SQL> update TV set ORA_ARCHIVE_STATE='ARCHIVING' where INSERT_TIME='20130811 09:50:48';
19368 rows updated.
SQL> select ORA_ARCHIVE_STATE,INSERT_TIME,count(*) from TV group by ORA_ARCHIVE_STATE,INSERT_TIME order by 2;
ORA_ARCHIVE_STATE INSERT_TIME COUNT(*)
-------------------- ----------------- ----------
0 20130811 09:04:30 1
0 20130811 09:08:27 1
0 20130811 09:22:30 1
0 20130811 09:39:40 1
0 20130811 09:45:22 1
20 20130811 09:50:44 19368
20 20130811 09:50:46 19368
20 20130811 09:50:47 19368
ARCHIVING 20130811 09:50:48 19368
在会话级别重新设置不显示归档数据，可以看到只要是ORA_ARCHIVE_STATE字段不为0的记录都不会显示。
SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;
Session altered.
SQL> select INSERT_TIME,count(*) from TV group by INSERT_TIME order by 1;
INSERT_TIME COUNT(*)
----------------- ----------
20130811 09:04:30 1
20130811 09:08:27 1
20130811 09:22:30 1
20130811 09:39:40 1
20130811 09:45:22 1
性能考虑，这一点数据库内归档与时间有效性是相同的，都只是对隐藏字段进行了filter操作。即使是只显示活跃数据，也仍然需要扫描全表。这一点在真实应用中可以通过创建索引来避免全表扫描，可以参看MOS Note: Potential SQL Performance Degradation When In Database Row Archiving (Doc ID 1579790.1)，也就是数据库内归档只应该在一个具备良好性能的SQL基础上对返回结果进行过滤，而不要期望归档的记录不参与扫描。
SQL> select * from TV;
INSERT_TIME
-----------------
20130811 09:04:30
20130811 09:08:27
20130811 09:22:30
20130811 09:39:40
20130811 09:45:22
Execution Plan
----------------------------------------------------------
Plan hash value: 1723968289
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 8044 | 102 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TV | 4 | 8044 | 102 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TV"."ORA_ARCHIVE_STATE"='0')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
375 consistent gets
0 physical reads
0 redo size
648 bytes sent via SQL*Net to client
543 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
数据库内归档可以跟时间有效性管理一起配合使用。在会话级别激活时间有效性，可以看到检索不再返回任何数据。执行计划中显示filter条件融合了数据库内归档跟时间有效性两层过滤。
SQL> exec dbms_flashback_archive.enable_at_valid_time('CURRENT');
PL/SQL procedure successfully completed.
SQL> select * from tv;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1723968289
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 6087 | 102 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TV | 3 | 6087 | 102 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."ORA_ARCHIVE_STATE"='0' AND ("T"."VALID_TIME_START"
IS NULL OR SYS_EXTRACT_UTC(INTERNAL_FUNCTION("T"."VALID_TIME_START"))<=S
YS_EXTRACT_UTC(SYSTIMESTAMP(6))) AND ("T"."VALID_TIME_END" IS NULL OR
SYS_EXTRACT_UTC(INTERNAL_FUNCTION("T"."VALID_TIME_END"))>SYS_EXTRACT_UTC
(SYSTIMESTAMP(6))))
Statistics
----------------------------------------------------------
34 recursive calls
8 db block gets
397 consistent gets
0 physical reads
0 redo size
347 bytes sent via SQL*Net to client
532 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
将时间有效期设置为20130811 09:39:50，根据上一篇文章我们设置的1分钟有效期，只有在20130811 09:39:40插入的这条活跃记录可以被显示出来。
SQL> exec dbms_flashback_archive.enable_at_valid_time('ASOF',to_date('20130811 09:39:50','YYYYMMDD HH24:MI:SS'));
PL/SQL procedure successfully completed.
SQL> select * from TV;
INSERT_TIME
-----------------
20130811 09:39:40
Execution Plan
----------------------------------------------------------
Plan hash value: 1723968289
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 6087 | 102 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TV | 3 | 6087 | 102 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."ORA_ARCHIVE_STATE"='0' AND ("T"."VALID_TIME_START"
IS NULL OR INTERNAL_FUNCTION("T"."VALID_TIME_START")<=TIMESTAMP'
2013-08-11 09:39:50.000000000') AND ("T"."VALID_TIME_END" IS NULL OR
INTERNAL_FUNCTION("T"."VALID_TIME_END")>TIMESTAMP' 2013-08-11
09:39:50.000000000'))
Statistics
----------------------------------------------------------
35 recursive calls
6 db block gets
398 consistent gets
0 physical reads
0 redo size
550 bytes sent via SQL*Net to client
543 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
结论：数据库内归档是一个Oracle利用隐藏字段实现的非常简单的功能，但是数据架构人员在规划的时候一定要考虑性能因素。