【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_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
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

SQL> desc employees_indbarch
 Name                                      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 DATE
 JOB_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 NUMBER
MANAGER_ID                  10 NUMBER
COMMISSION_PCT               9 NUMBER
SALARY                       8 NUMBER
JOB_ID                       7 VARCHAR2
HIRE_DATE                    6 DATE
PHONE_NUMBER                 5 VARCHAR2
EMAIL                        4 VARCHAR2
LAST_NAME                    3 VARCHAR2
FIRST_NAME                   2 VARCHAR2
EMPLOYEE_ID                  1 NUMBER
ORA_ARCHIVE_STATE              VARCHAR2

SQL> 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                                    1
Typ=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: 12
col  0: [ 1]  30
col  1: [ 3]  c2 03 34
col  2: [ 5]  53 63 6f 74 74
col  3: [ 5]  54 69 67 65 72
col  4: [23]
 73 63 6f 74 74 2e 74 69 67 65 72 40 65 78 61 6d 70 6c 65 2e 63 6f 6d
col  5: *NULL*
col  6: [ 7]  78 6d 05 15 01 01 01
col  7: [ 7]  49 54 5f 50 52 4f 47  
col  8: [ 2]  c3 06
col  9: *NULL*
col 10: [ 3]  c2 02 04
col 11: [ 2]  c1 3d
tab 0, row 1, @0x1ecc
tl: 70 fb: --H-FL-- lb: 0x2  cc: 12
col  0: [ 2]  32 30
col  1: [ 3]  c2 03 35
col  2: [ 4]  4a 61 6e 65
col  3: [ 4]  4c 69 6f 6e
col  4: [21]
 6a 61 6e 65 2e 6c 69 6f 6e 40 65 78 61 6d 70 6c 65 2e 63 6f 6d
col  5: *NULL*
col  6: [ 7]  78 6d 06 0b 01 01 01
col  7: [ 7]  49 54 5f 50 52 4f 47
col  8: [ 2]  c3 06
col  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”
*/

Comments

  1. wangxy2 says

    ILM is not supported with OLTP table compression for in-database archiving.ILM指的是什么?

Trackbacks

  1. […] 【Oracle Database 12c新特性】 In-Database Archiving数据库内归档 […]

Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号