Oracle Database 12C新特性 In-Database Archiving数据库内归档
Oracle Database 12C中引入了 In-Database Archiving的新特性, 该特性允许用户通过对表上的数据行标记为inactive不活跃的,以归
Oracle Database 12C中引入了 In-Database Archiving的新特性, 该特性允许用户通过对表上的数据行标记为inactive不活跃的,以归档数据。
这些inactive的数据行可以通过压缩进一部优化,且对应用来说默认不可见。 可以通过一个session级别的参数来控制数据的可见与否。
通过In-Database Archiving数据库内归档特性可以在单库中存放更长周期的数据, 而损耗很少的应用性能。 归档数据还可以通过压缩来进一步提升备份效能。
要管理一张表的In-Database Archiving,必须为表启用ROW ARCHIVAL 并操作ORA_ARCHIVE_STATE 这个隐藏字段 。 此外用户还可以指定session级别的 ROW ARCHIVAL VISIBILITY为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);
commit;
/* Show all the columns in the table, including hidden columns */
SELECT SUBSTR(COLUMN_NAME,1,22), SUBSTR(DATA_TYPE,1,20), 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';
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
上一篇: Mysql中间件研究(Atlas,cobar,TDDL)_MySQL
下一篇: php 安全_PHP