In-Database Archiving – A Little Known Oracle Database Feature

In-Database Archiving allows users, and applications, to set the archive state for individual rows. Rows that have been marked as archived stay where they are, but are not visible unless the session is enabled to see archived data. To manage In-Database Archiving for a table, you must enable ROW ARCHIVAL for the table using either CREATE TABLE or ALTER TABLE, for example:

CREATE TABLE residents (
        id          NUMBER,
       street     VARCHAR2(50)
CONSTRAINT tbb1_pk PRIMARY KEY (id))
ROW ARCHIVAL;

Enabling in-database archiving causes the addition of a system generated hidden column called ORA_ARCHIVE_STATE.

SELECT column_id,
         column_name,
               data_type,
            data_length,
       hidden_column
FROM   user_tab_cols
WHERE  table_name = ‘RESIDENTS’
ORDER BY column_id;

COLUMN_ID     COLUMN_NAME     DATA_TYPE     DATA_LENGTH     HID
—————–      ———————–      —————-      ———————     —–
                   1                             ID          NUMBER                            22     NO
                   2                   STREET      VARCHAR2                            50     NO
ORA_ARCHIVE_STATE                     VARCHAR2                        4000   YES

By default, this column is populated with the value ‘0’ for each row. When the value for ORA_ARCHIVE_STATE is set to ‘0’ then that row is visible to applications, meaning that the row is active and visible via a standard query. In the example below, all four records have ORA_ARCHIVE_STATE is set to ‘0’.

SELECT id, street FROM residents;

       ID           STREET                   
———-    —————-
         1         Maple St
         2           Main St
         3            Elm St
         4        Poplar St

To make rows invisible to applications, update the ORA_ARCHIVE_STATE system generated hidden column with the value ‘1’. In the example below, two records (ID #’s 2 and 4) have been updated and have had their ORA_ARCHIVE_STATE set to ‘1’, making those records invisible to applications, so now the same query would return the following results:

SELECT id, street FROM residents;

       ID          STREET
———-   —————-
           1       Maple St
           3          Elm St

Note that the ORA_ARCHIVE_STATE column can be set to any string value other than ‘0’ to archive the data, the DBMS_ILM package uses the following constants:

ARCHIVE_STATE_ACTIVE=’0′
ARCHIVE_STATE_ARCHIVED=’1′

Optionally, you can specify either ACTIVE or ALL for the ROW ARCHIVAL VISIBILITY session parameter to make archived rows visible or invisible for the session. If you specify ACTIVE, then the database will consider only active rows when performing queries on tables that are enabled for row archival. This is the default.

ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;

If you specify ALL, then the database will consider all rows when performing queries on tables that are enabled for row archival.

ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;

As seen above, In-database archiving adds a hidden column to the table to specify the visibility of each row. For every query and DML, Oracle then injects a predicate to limit the query/DML to only the visible rows. This added predicate could affect the optimizer, indexes, etc. just like any other predicate.

In-Database Archiving is available starting with Oracle Database 12.1.0.1.
For more information about In-Database Archiving, see here.