[TOC] # 您需要知道的 - 数据库内归档功能，在 12.1.0.1 版本中引入，可以通过将表中的行标记为非活动的方式来对其进行归档隐藏 - 您必须使用 ROW ARCHIVAL 关键字来为表启用数据库内归档功能 - 当使用 ROW ARCHIVAL 子句创建表或修改表时，会在表中生成隐藏列 ORA_ARCHIVE_STATE - 隐藏列 ORA_ARCHIVE_STATE 值分为 0 和 非0 值，其中 0 为默认值，意味着 ACTIVE 活动 - 对于行存档可见性会话参数有两个：ACTIVE 和 ALL # 1 创建表与修改表语句 - 创建表启用 In-Database Archiving ```sql SQL> alter session set container=pdb1; Session altered. SQL> create table henry(id number,name varchar2(10)) row archival; Table created. ``` - 修改表启用 In-Database Archiving ```sql SQL> create table henry(id number,name varchar2(10)); Table created. SQL> alter table henry row archival; Table altered. ``` # 2 插入数据并提交 ```sql SQL> insert into henry values(1,'Tom'); 1 row created. SQL> insert into henry values(2,'Jerry'); 1 row created. SQL> commit; Commit complete. ``` # 3 查看数据字典表中所有的列信息 ```sql SQL> set linesize 300 SQL> col name for a20 SQL> col type for a10 SQL> select column_name as name,data_type as type,column_id as col_id,segment_column_id as scolid,hidden_column as hcol,char_length from user_tab_cols where table_name = 'HENRY'; NAME TYPE COL_ID SCOLID HCOL CHAR_LENGTH -------------------- ---------- ---------- ---------- ------ ----------- ID NUMBER 1 1 NO 0 NAME VARCHAR2 2 2 NO 10 SYS_NC00003$ RAW 3 YES 0 ORA_ARCHIVE_STATE VARCHAR2 4 YES 4000 ``` # 4 设置 Active 可见性以显示活动记录 - ORA_ARVHIVE_STATE 默认值为 0 ，表示 ACTIVE 活动的 ```sql SQL> col ora_archive_state for a20 SQL> alter session set row archival visibility = active; # 会话可见性默认为 ACTIVE SQL> select id,name,ora_archive_state from henry; ID NAME ORA_ARCHIVE_STATE ---------- -------------------- -------------------- 1 Tom 0 2 Jerry 0 ``` # 5 更新 ORA_ARCHIVE_STATE 字段值 - 将 ORA_ARCHIVE_STATE 更新其他非 0 任意值设置 INACTIVE 非活动状态 ```sql SQL> update henry set ora_archive_state = '1' where id = 1; 1 row updated. SQL> commit; Commit complete. ``` # 6 现在查询仅有活动记录 ```sql SQL> select id,name,ora_archive_state from henry; ID NAME ORA_ARCHIVE_STATE ---------- -------------------- -------------------- 2 Jerry 0 ``` # 7 设置 All 可见性以显示所有记录 ```sql SQL> alter session set row archival visibility = all; Session altered. SQL> select id,name,ora_archive_state from henry; ID NAME ORA_ARCHIVE_STATE ---------- -------------------- -------------------- 1 Tom 1 2 Jerry 0 ``` [End]