What is “In Database Archiving”
How can we deal with historic and non-active data and keeping most of your data online within the database itself?
With In Database Archiving, you can keep both operational and historical data together in the same database, but limit your applications so they access only the operational active data.
A new row property helps separate active and non-active data in the table and lets you
archive rows by marking them inactive. The data remains in the table and you can compress it, but to the application this part of the inactive data remains hidden.
You can also use a second capability, called Temporal Validity, to distinguish active rows from inactive rows in the same table. It uses the Oracle 11g Flashback Data Archive (FDA) that has been renamed Temporal with a 12c new feature.
Temporal Validity adds two “date” columns in the table.
The In-Database Archiving and Temporal Validity capabilities let you control the validity and visibility of data.
Hybrid Columnar Compression
Oracle’s Hybrid Columnar Compression (HCC) is designed for in-database archiving.
It supports 2 types of compression:
– Warehouse Compression can compress data up to 10x, it will improve query by reducing the size of the data queried (active data)
– Archive Compression can achieve a 15x to 50x compression ratio. Instead of compressing the repeating values found within rows of data to save storage space, HCC stores data by column.
How to enable/disable/view “In Database Archiving”?
– In order to enable In-Database Archiving in a table, you have to enable row archival by adding the hidden column ORA_ARCHIVE_STATE (0-1) during table creation.
CREATE TABLE ORDER_HISTORY (ORDER_ID NUMBER, […], ) row archival;
The row inactivity is managed by the temporal validity, when a row is rarely updated or accessed it is marked as inactive and then archived
To view active/non active state of rows in a table, you have to explicitly specify the column:
SELECT ora_archive_state, order_id FROM ORDER_HISTORY;
0: Active 1: non active
To update the value of the ORA_ARCHIVE_STATE:
update ORDER_HISTORY set ora_archive_state = dbms_ilm.archivestatename(1) where […];
To disable In Database Archiving:
ALTER TABLE ORDER_HISTORY NO ROW ARCHIVAL;
By default when you query a table where the In Database Archiving feature is enabled, Oracle returns only the active rows, to see all rows:
ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;
Vincent Fenoll – Oracle DBA Montreal