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

51 阅读 0 评论 34 点赞

我是靠谱客的博主甜美小蝴蝶，最近开发中收集的这篇文章主要介绍Oracle Database 12c新特性 In-Database Archiving数据库内归档，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

【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”
*/