Guenadi N Jilevski's Oracle BLOG

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.

 

October 14, 2016 - Posted by gjilevski | oracle