How to Use In-Database Archiving

Why In-Database Archiving

The simple goal of in-database archiving is to assign values to the data based on business rules of whether the data is current or marked as non-active. As the value of the data lowers over time, the related data can be moved to less costly storage. The challenges are:

• Databases grow; rarely do they get smaller
• Data growth rates are accelerating
• The old data may need to be instantly available in the database at a future time

In-database archiving is a possible solution.

Setting Up Row Archival

Use in-database archiving to distinguish active rows from non-active within the same table. It is also possible to compress the non-active rows as they are moved. Compression requires the Advanced Compression option.

To enable row archival, add the hidden column, ora_archive_state:

CREATE TABLE emp
(empno NUMBER(7),
fullname VARCHAR2(40),
job VARCHAR2(9),
mgr NUMBER(7))
ROW ARCHIVAL; — adds the ora_archive_state column

An existing table can be altered for In-Database Archiving:

ALTER TABLE emp ROW ARCHIVAL;

The row archival attribute indicates whether a row is active or non-active. By default, when a row is inserted, it is set as active, and the ora_archive_state value is ‘0’. After a period of time, the row may be less frequently accessed and is rarely updated, but still considered as active. It needs to be retained for records retention and/or compliance purposes. It is, therefore, considered to be in a non-active state.  The value ‘1’ – or any value other than ‘0’ – in the ora_archive_state column would reflect the non-active state. Update ora_archive_state to ‘1’ for row archiving. ACTIVE is the default value.

Using Row Archival

View ora_archive_state column:

When it is determined that the rows should not be active, set the rows to the archive state. This could be done with a batch job scheduled to run during the maintenance window.

UPDATE emp
SET ora_archive_state = 1
WHERE  empno < 100;

Set rows back to active state if needed for active use:

UPDATE emp
SET ora_archive_state = 0;

Setting Session Visibility

To view active rows only:

View all rows:

ALTER SESSION SET ROW ARCHIVAL VISIBILITY  = ALL;

To return to active rows only:

ALTER SESSION SET ROW ARCHIVAL VISIBILITY  = ACTIVE;

Disabling Row Archival

When row archival is disabled, the ora_archive_state column is automatically removed. To disable row archival:

ALTER TABLE emp NO ROW ARCHIVAL;

In my next blog, I will be discussing Temporal History.