In database row archiving in Oracle 12c
In the article you will have a look at the new Oracle 12c feature called in database row archiving available since Oracle 12c. In Oracle versions prior to 12c all table data was an active visible set of data, that is subject to DML performed by the application. In Oracle 12c the In database row archiving feature enable us to have two sets of table data an active visible set of data that coexists with archived and invisible set of data. The in database row archiving is a table feature that get enabled at table level with the ROW ARCHIVAL clause of create table or alter table. Once the feature enabled a new hidden table column ORA_ARCHIVE_STATE appears. This column can be used in SELECT list and can be updated but is not visible in describe issued from sqlplus prompt. It is the ORA_ARCHIVE_STATE column value that determines whether the row belongs to the active set of data or the archive invisible set of database. When rows get inserted into a table the ORA_ARCHIVE_STATE is 0. By modifying the ORA_ARCHIVE_STATE to 1 the row is transferred into the invisible archived set of table data. CTAS does create a new table but does not propagated the In database row archiving feature from the source table. The application can control the visibility to the active and invisible archived set of data using the ALTER SESSION SET ROW ARCHIVAL VISIBILITY [ALL/ACTIVE] statement at session level.
I will use the EMP table in SCOTT schema. After enabling the In database row archiving describe does not display the ORA_ARCHIVE_STATE column.
SQL> connect scott/tiger
Connected.
SQL> desc emp
Name Null? Type
—————————————– ——– —————————-
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> alter table emp row archival;
Table altered.
SQL> desc emp
Name Null? Type
—————————————– ——– —————————-
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL>
However, ORA_ARCHIVE_STATE is visible for select statements if directly referenced.
SQL> select empno, ename, ora_archive_state from emp where rownum < 4;
EMPNO ENAME
———- ———-
ORA_ARCHIVE_STATE
————————————————————
7369 SMITH
0
7499 ALLEN
0
7521 WARD
0
SQL> select count(*) from emp;
COUNT(*)
———-
14
SQL>
Let’s archive all table rows.
SQL> update emp set ora_archive_state=1;
14 rows updated.
SQL> commit;
Commit complete.
SQL> select empno, ename, ora_archive_state from emp whererownum < 4;
no rows selected
Let’s look at toggling the visibility of the tables’ data.
We will see all table data if we set row archival visibility = all.
SQL> alter session set row archival visibility = all;
Session altered.
SQL> select empno, ename, ora_archive_state from emp where rownum < 4;
EMPNO ENAME
———- ———-
ORA_ARCHIVE_STATE
————————————————————
7369 SMITH
1
7499 ALLEN
1
7521 WARD
1
SQL> SQL> select count(*) from emp;
COUNT(*)
———-
14
Let’s show the active set (the default value).
SQL> alter session set row archival visibility = active;
Session altered.
SQL> select empno, ename, ora_archive_state from emp where rownum < 4;
no rows selected
SQL> select count(*) from emp;
COUNT(*)
———-
0
SQL>
CTAS does not propagate the In database row archiving feature to the target table.
SQL> create table emp_dup as select * from emp;
Table created.
SQL>
SQL> select empno, ora_archive_state from emp_Dup;
select empno, ora_archive_state from emp_Dup
*
ERROR at line 1:
ORA-00904: “ORA_ARCHIVE_STATE”: invalid identifier
SQL>
SQL> alter table emp_dup row archival;
Table altered.
SQL> select empno, ora_archive_state from emp_Dup;
EMPNO
———-
ORA_ARCHIVE_STATE
————————————————————
7369
0
7499
0
7521
0
EMPNO
———-
ORA_ARCHIVE_STATE
————————————————————
7566
0
7654
0
7698
0
EMPNO
———-
ORA_ARCHIVE_STATE
————————————————————
7782
0
7788
0
7839
0
EMPNO
———-
ORA_ARCHIVE_STATE
————————————————————
7844
0
7876
0
7900
0
EMPNO
———-
ORA_ARCHIVE_STATE
————————————————————
7902
0
7934
0
14 rows selected.
SQL>
Summary:
In the article we had a look at the new Oracle 12c feature In database row archiving.
No comments yet.
-
Archives
- February 2017 (1)
- November 2016 (1)
- October 2016 (1)
- May 2016 (2)
- March 2016 (3)
- December 2014 (2)
- July 2014 (1)
- June 2014 (6)
- May 2014 (5)
- February 2014 (1)
- December 2012 (2)
- November 2012 (8)
-
Categories
-
RSS
Entries RSS
Comments RSS
Leave a Reply