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.

maltcasino maltcasino maltcasino bedava bahis güvenilir bahis siteleri canlı bahis siteleri
bedava bahis Yatırımsız Deneme Bonusu bonus veren siteler