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.
