In-Database Archiving

In-Database Archiving

Oracle Database 12c introduces In-Database Archiving to archive the low priority data in a table. The inactive data remains in the database but is not visible to the application.

You can mark old data for archival, which is not actively required in the application except for regulatory purposes. Although the archived data is not visible to the application, it is available for querying and manipulation. In addition, the archived data can be compressed to improve backup performance.

A table can be enabled by specifying the ROW ARCHIVAL clause at the table level, which adds a hidden column ORA_ARCHIVE_STATE to the table structure. The column value must be updated to mark a row for archival.

 For example:

/*Create a table with row archiving*/

CREATE TABLE t_row_arch(

x number,

y number,

z number) ROW ARCHIVAL

/

When we query the table structure in the USER_TAB_COLS view, we find an additional hidden column, which Oracle implicitly adds to the table:

/*Query the columns information from user_tab_cols view*/

SELECT column_id,column_name,data_type, hidden_column

FROM user_tab_cols

WHERE table_name='T_ROW_ARCH'

/

COLUMN_ID COLUMN_NAME  DATA_TYPE  HID

---------- ------------------ ---------- ---

   ORA_ARCHIVE_STATE    VARCHAR2    YES

       1   X                 NUMBER     NO

       2   Y                 NUMBER     NO

       3   Z                 NUMBER     NO

 Let us create test data in the table:

/Insert test data in the table*/

BEGIN

INSERT INTO t_row_arch VALUES (10,20,30);

INSERT INTO t_row_arch VALUES (11,22,33);

INSERT INTO t_row_arch VALUES (21,32,43);

INSERT INTO t_row_arch VALUES (51,82,13);

commit;

END;

For testing purpose, let us archive the rows in the table where X > 50 by updating the ora_archive_state column:

/*Update ORA_ARCHIVE_STATE column in the table*/

UPDATE t_row_arch

SET ora_archive_state = 1

WHERE x > 50

/

COMMIT

/

By default, the session displays only the active records from an archival-enabled table:

/*Query the table*/

SELECT *

FROM t_row_arch

/

   X     Y         Z

------ -------- ----------

10     20      30

11     22       33

21     32       43

If you wish to display all the records, change the session setting:

 /*Change the session parameter to display the archived records*/

ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL

/

Session altered.

/*Query the table*/

SELECT *

FROM t_row_arch

/

   X     Y         Z

---------- ---------- ----------

10     20       30

11     22       33

21     32       43

51     82       13

Chandrasekar S.