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」
*/

出處:HTTP://www.askmaclean.com/archives/12c-in-database-archiving.html
創作者介紹
創作者 shadow 的頭像
shadow

資訊園

shadow 發表在 痞客邦 留言(0) 人氣()