转载

oracle 12c 数据归档即Using In-Database Archiving feature

在oracle 12c 通过Using In-Database Archiving feature 特性，来启到分离在线数据和历史数据的作用， 即数据归档，应用可以有选择性的访问在线数据或者历史数据，要启用这个特性，在建表的时候启用ROW ARCHIVAL， 并操作ORA_ARCHIVE_STATE 这个隐藏字段，此外用户还可以指定会话级别的 ROW ARCHIVAL VISIBILITY为ACTIVE  或者ALL 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);  /* Show all the columns in the table, including hidden columns */ 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

正文到此结束