Oracle Database 12C新特性 In-Database Archiving数据库内归档

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_LENGTHFROM 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_LENGTHFROM 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 4000EMPLOYEE_ID NUMBER 1 2 2 NO 0FIRST_NAME VARCHAR2 2 3 3 NO 20LAST_NAME VARCHAR2 3 4 4 NO 25EMAIL VARCHAR2 4 5 5 NO 25PHONE_NUMBER VARCHAR2 5 6 6 NO 20HIRE_DATE DATE 6 7 7 NO 0JOB_ID VARCHAR2 7 8 8 NO 10SALARY NUMBER 8 9 9 NO 0COMMISSION_PCT NUMBER 9 10 10 NO 0MANAGER_ID NUMBER 10 11 11 NO 0DEPARTMENT_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 20SQL> desc employees_indbarchName Null？ Type----------------------------------------- -------- ----------------------------EMPLOYEE_ID NOT NULL NUMBER（6）FIRST_NAME VARCHAR2（20）LAST_NAME NOT NULL VARCHAR2（25）EMAIL NOT NULL VARCHAR2（25）PHONE_NUMBER VARCHAR2（20）HIRE_DATE NOT NULL DATEJOB_ID NOT NULL VARCHAR2（10）SALARY NUMBER（8,2）COMMISSION_PCT NUMBER（2,2）MANAGER_ID NUMBER（6）DEPARTMENT_ID NUMBER（4）1* select COLUMN_NAME,COLUMN_ID,DATA_TYPE from dba_tab_cols where table_name=upper（"employees_indbarch"）SQL> /COLUMN_NAME COLUMN_ID DATA_TYPE-------------------- --------- --------------------DEPARTMENT_ID 11 NUMBERMANAGER_ID 10 NUMBERCOMMISSION_PCT 9 NUMBERSALARY 8 NUMBERJOB_ID 7 VARCHAR2HIRE_DATE 6 DATEPHONE_NUMBER 5 VARCHAR2EMAIL 4 VARCHAR2LAST_NAME 3 VARCHAR2FIRST_NAME 2 VARCHAR2EMPLOYEE_ID 1 NUMBERORA_ARCHIVE_STATE VARCHAR2SQL> select dump（ORA_ARCHIVE_STATE,16）,dump（EMPLOYEE_ID,16）,dbms_rowid.rowid_block_number（rowid）,dbms_rowid.rowid_relative_fno（rowid） from employees_indbarch;DUMP（ORA_ARCHIVE_STA DUMP（EMPLOYEE_ID,16） DBMS_ROWID.ROWID_BLOCK_NUMBER（ROWID） DBMS_ROWID.ROWID_RELATIVE_FNO（ROWID）-------------------- -------------------- ------------------------------------ ------------------------------------Typ=1 Len=1: 30 Typ=2 Len=3: c2,3,34 109465 1Typ=1 Len=2: 32,30 Typ=2 Len=3: c2,3,35 109465 1 SQL> alter system flush buffer_cache;System altered.SQL> alter system dump datafile 1 block 109465;System altered.tl: 73 fb: --H-FL-- lb: 0x0 cc: 12col 0: [ 1] 30col 1: [ 3] c2 03 34col 2: [ 5] 53 63 6f 74 74col 3: [ 5] 54 69 67 65 72col 4: [23]73 63 6f 74 74 2e 74 69 67 65 72 40 65 78 61 6d 70 6c 65 2e 63 6f 6dcol 5: *NULL*col 6: [ 7] 78 6d 05 15 01 01 01col 7: [ 7] 49 54 5f 50 52 4f 47 col 8: [ 2] c3 06col 9: *NULL*col 10: [ 3] c2 02 04col 11: [ 2] c1 3dtab 0, row 1, @0x1ecctl: 70 fb: --H-FL-- lb: 0x2 cc: 12col 0: [ 2] 32 30col 1: [ 3] c2 03 35col 2: [ 4] 4a 61 6e 65col 3: [ 4] 4c 69 6f 6ecol 4: [21]6a 61 6e 65 2e 6c 69 6f 6e 40 65 78 61 6d 70 6c 65 2e 63 6f 6dcol 5: *NULL*col 6: [ 7] 78 6d 06 0b 01 01 01col 7: [ 7] 49 54 5f 50 52 4f 47col 8: [ 2] c3 06col 9: *NULL*col 10: [ 3] c2 02 04 col 11: [ 2] c1 3d可以看到这里 ORA_ARCHIVE_STATE是实际存放在块里的row piece上的第一个字段，类型为varchar2（4000）;
In-Database Archiving的限制：§ ILM is not supported with OLTP table compression for in-database archiving. Segment-level ILM and compression is supported if partitioned on the ORA_ARCHIVE_STATE column.ORA_ARCHIVE_STATE相关：/* constants specifying ROW ARCHIVAL state */
archive_state_active constant varchar2（1） := ‘0’;
archive_state_archived constant varchar2（1） := ‘1’;/*
* description – Given a value for the ORA_ARCHIVE_STATE column this
* function returns the mapping for the value.
*
* value – “0”, “1” or other values from the ORA_ARCHIVE_STATE column of
* a row archival enabled table
* returns either “archive_state_active” or “archive_state_archived”
*/更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址