In-Database Archiving was a new feature in Oracle 12.1. Instead
physically removing the rows with delete, you can mark those rows as archived/inactive. Rows that have been marked as inactive can of course be set as active again. This can all be accomplished with the “Row Archival” clause.

When issuing the Row Archival clause, Oracle will then create a hidden column called ora_archive_state. When a row is inserted the default value for ora_archive_state is 0, meaning active. Any value except 0 in ora_archive_state makes that row inactive.

Let’s make an example. Create a table with row archival and insert some rows.( Haven’t used INSERT ALL? Read my previous blog post on this )

create table emojis(smiley_name varchar2(100),tale varchar2(200),
constraint emojis_pk primary key (smiley_name))row archival;

insert all
into emojis(smiley_name,tale)
values('Grinning','A yellow face grinning')

into emojis(smiley_name,tale)
values('Smiling','A yellow face with a modest smile, rosy cheeks')

into emojis(smiley_name,tale)
values('Disappointed','A yellow face with a frown and closed, downcast eyes')

into emojis(smiley_name,tale)
values('Angry','A red face with a frowning mouth and eyes and eyebrows scrunched downward in anger')
select 1 from dual;

Query the table and see the status of the ora_archive_state

select smiley_name,ora_archive_state from emojis;

smiley_name ora_archive_state
--------------------------------
Grinning            0
Smiling             0
Disappointed        0
Angry               0

Let’s put the “Angry” row in an inactive state and make a query to validate the row archival operation.

update emojis
set ora_archive_state=1
where smiley_name='Angry';

select smiley_name from emojis;

smiley_name
----------------
Grinning
Smiling
Disappointed

As expected the “Angry” row isn’t visible anymore. Let’s check the value for the column ora_archive_state.

select smiley_name,ora_archive_state from emojis;

smiley_name ora_archive_state
--------------------------------
Grinning            0
Smiling             0
Disappointed        0

Hmm, I really can’t see what has happened to my “Angry” row. To be able to see what has happened, we can change the session visibility.

alter session set row archival visibility  = all;
Session altered.
select smiley_name,ora_archive_state from emojis;

smiley_name ora_archive_state
--------------------------------
Grinning            0
Smiling             0
Disappointed        0
Angry               1

The default for “row archival visibility” is active. If I want to put back my “Angry” row which is inactive to an active state. The “row archival visibility” must be set to all;

alter session set row archival visibility  = active;
Session altered.
update emojis
set ora_archive_state=0
where smiley_name='Angry';

0 rows updated

alter session set row archival visibility  = all;
Session altered.
update emojis
set ora_archive_state=0
where smiley_name='Angry';

1 row updated.

If you want to disable Row Archving.

alter table emojis no row archival;
Table EMOJIS altered.

In-Database Archiving is a neat feature. You can hide the inactive data from the application, compress it. This is definitely an interesting tool for potentially boosting performance.

Over & Out