Oracle has introduced in-database archiving in its current release ( 12c) . Prior to this version , we would have status flag something "ISACTIVE" to indicate if this is active record or not . With Oracle 12c , we can use system defined function for it using dbms_ilm , to implement information lifecycle management . The advantage , I am seeing here is Oracle can perform the compression on the archived records co-existing with the active records.
Let us look at the example.
SQL> Create table emp as Select * from scott.emp;
Table created.
SQL> Set pagesize90
SQL> set linesize 1000
SQL> select * From emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> Select e.* , ora_Archive_state from emp e;
Select e.* , ora_Archive_state from emp e
*
ERROR at line 1:
ORA-00904: "ORA_ARCHIVE_STATE": invalid identifier
Next , we can enable in-database archiving
SQL> alter table emp row archival ;
Table altered.
To create the table with "row archival" , we could use the regular syntax suffixed with ROW ARCHIVAL as below.
Create table emp (
empno number( 4, 0 ) ,
...
....
)
row archival;
Let us execute the following SQL.
Select e.* , ora_Archive_state from emp e;
Below the value of "1" ( used in dbms_ilm) is to archive , 0 is for active ( or new ) records.
SQL> Update emp
2 set ora_archive_state = dbms_ilm.archivestatename(1)
3 where comm = 0 ;
1 row updated.
SQL> commit;
Commit complete.
SQL> Select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
13 rows selected.
As you can see , only 13 records are returned . The record of empno 7844 disappears.
To look at all the records ( active / archive) , we change session setting of " ROW ARCHIVAL VISIBLITY" to ALL.
SQL> alter session set row archival visibility=all ;
Session altered.
SQL> Select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
We can turn off by the in-database archiving as below.
SQL> alter table emp no row archival ;
I am still trying to see , if there is any easy way to identify the list of tables that have been enabled for in-line archiving or not . Will keep you updated of my findings.
Comments Welcome.
No comments:
Post a Comment