[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]

ITPub博客

G009-ORACLE-ASK Using In-Database Archiving

最新文章