淘宝客网站需要多大空间/百度指数有哪些功能
在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/，如需转载，请注明出处，否则将追究法律责任。