How to Use In-Database Archiving

In this posting, we are continuing with the theme of Information Lifecycle Management (ILM).  Another option in Oracle 12c, is In-Database Archiving, also called Row Archival.

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.

in-database archiving

Using Row Archival

View ora_archive_state column:

dec1-2

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:

dec1-3

View all rows:

ALTER SESSION SET ROW ARCHIVAL VISIBILITY  = ALL;

dec1-4

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;

dec1-5

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

image sources

  • dec1-1: Kent Hinckley
  • dec1-2: Kent Hinckley
  • dec1-3: Kent Hinckley
  • dec1-4: Kent Hinckley
  • dec1-5: Kent Hinckley

Type to search blog.learningtree.com

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.