oracle 12c创建归档,oracle 12c 数据归档 即Using In-Database Archiving feature

在oracle 12c 通过Using In-Database Archiving feature 特性，来启到分离在线数据和历史数据的作用，

即数据归档，应用可以有选择性的访问在线数据或者历史数据，要启用这个特性，在建表的时候启用ROW ARCHIVAL，

并操作ORA_ARCHIVE_STATE 这个隐藏字段，此外用户还可以指定会话级别的 ROW ARCHIVAL VISIBILITY为ACTIVE  或者ALL

active就是看到在线数据,ALL的话就是全部数据。

/* Set visibility to ACTIVE to display only active rows of a table.*/

ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;

CREATE TABLE employees_indbarch

(employee_id NUMBER(6) NOT NULL,

first_name VARCHAR2(20), last_name VARCHAR2(25) NOT NULL,

email VARCHAR2(25) NOT NULL, phone_number VARCHAR2(20),

hire_date DATE NOT NULL, job_id VARCHAR2(10) NOT NULL, salary NUMBER(8,2),

commission_pct NUMBER(2,2), manager_id NUMBER(6), department_id NUMBER(4)) ROW ARCHIVAL;

INSERT INTO employees_indbarch(employee_id, first_name, last_name, email,

hire_date, job_id, salary, manager_id, department_id)

VALUES (251, 'Scott', 'Tiger', 'scott.tiger@example.com', '21-MAY-2009',

'IT_PROG', 50000, 103, 60);

INSERT INTO employees_indbarch(employee_id, first_name, last_name, email,

hire_date, job_id, salary, manager_id, department_id)

VALUES (252, 'Jane', 'Lion', 'jane.lion@example.com', '11-JUN-2009',

'IT_PROG', 50000, 103, 60);

/* Show all the columns in the table, including hidden columns */

SELECT SUBSTR(COLUMN_NAME,1,22) NAME, SUBSTR(DATA_TYPE,1,20) DATA_TYPE, COLUMN_ID AS COL_ID,

SEGMENT_COLUMN_ID AS SEG_COL_ID, INTERNAL_COLUMN_ID AS INT_COL_ID, HIDDEN_COLUMN, CHAR_LENGTH

FROM USER_TAB_COLS WHERE TABLE_NAME='EMPLOYEES_INDBARCH';

NAME DATA_TYPE COL_ID SEG_COL_ID INT_COL_ID HID CHAR_LENGTH

---------------------- -------------------- ---------- ---------- ---------- --- -----------

ORA_ARCHIVE_STATE VARCHAR2 1 1 YES 4000

EMPLOYEE_ID NUMBER 1 2 2 NO 0

FIRST_NAME VARCHAR2 2 3 3 NO 20

LAST_NAME VARCHAR2 3 4 4 NO 25

EMAIL VARCHAR2 4 5 5 NO 25

PHONE_NUMBER VARCHAR2 5 6 6 NO 20

HIRE_DATE DATE 6 7 7 NO 0

JOB_ID VARCHAR2 7 8 8 NO 10

SALARY NUMBER 8 9 9 NO 0

COMMISSION_PCT NUMBER 9 10 10 NO 0

MANAGER_ID NUMBER 10 11 11 NO 0

DEPARTMENT_ID NUMBER 11 12 12 NO 0

/* Decrease the ORA_ARCHIVE_STATE column size to improve formatting in queries */

COLUMN ORA_ARCHIVE_STATE FORMAT a18;

/* The default value for ORA_ARCHIVE_STATE is '0', which means active */

SELECT employee_id, ORA_ARCHIVE_STATE FROM employees_indbarch;

EMPLOYEE_ID ORA_ARCHIVE_STATE

----------- ------------------

251 0

252 0

/* Insert a value into ORA_ARCHIVE_STATE to set inactive */

UPDATE employees_indbarch SET ORA_ARCHIVE_STATE = '20' WHERE employee_id = 252;

/* Only active records are in the following query */

SELECT employee_id, ORA_ARCHIVE_STATE FROM employees_indbarch;

EMPLOYEE_ID ORA_ARCHIVE_STATE

----------- ------------------

251 0

/* Set visibility to ALL to display all records */

ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;

SELECT employee_id, ORA_ARCHIVE_STATE FROM employees_indbarch;

EMPLOYEE_ID ORA_ARCHIVE_STATE

----------- ------------------

251 0

252 20

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7199859/viewspace-2058720/，如需转载，请注明出处，否则将追究法律责任。