This article presents following new feature of Oracle Database 12C

In-Database Archiving – this option enables to mark records in a table as not active (called later archive records). As default archive records are not visible in Oracle sessions. The records can be later compressed or deleted.

The reason to add such option was to keep both versions of records (active and not active) in the same table instead of making backup(not active) on tape and delete them(not active)  from a table.

In-Database Archiving

To turn this option ROW ARCHIVAL must be specified during creation of a table (or by ALTER TABLE command showed later).

CREATE TABLE test_tbl
( 
  id NUMBER
)
ROW ARCHIVAL;

It will create hidden column ORA_ARCHIVE_STATE that is used to mark records as active(default) or not active.

SELECT column_id, table_name, column_name, hidden_column
FROM user_tab_cols
WHERE table_name='TEST_TBL'
order by column_id;

 COLUMN_ID TABLE_NAME  COLUMN_NAME        HIDDEN_COLUMN
---------- ----------- ------------------ -------------
         1 TEST_TBL    ID                 NO            
           TEST_TBL    ORA_ARCHIVE_STATE  YES

“In-Database Archive” option can be turned on/off using ALTER TABLE command

ALTER TABLE test_tbl NO ROW ARCHIVAL;
ALTER TABLE test_tbl ROW ARCHIVAL;

Once new data are inserted into the table all records as default are marked as active (always visible in a session).

• active(default option) records – ORA_ARCHIVE_STATE value 0
• not active records – ORA_ARCHIVE_STATE value 1

INSERT INTO test_tbl 
SELECT LEVEL
FROM dual
CONNECT BY LEVEL < 5;

COMMIT;

--hidden columns as default are not visible
SELECT * FROM test_tbl;

        ID
----------
         1 
         2 
         3 
         4
-- but still you can refer to them explicit 
SELECT ID, ORA_ARCHIVE_STATE 
FROM test_tbl;

ID  ORA_ARCHIVE_STATE 
--- ------------------
  1 0                 
  2 0                 
  3 0                 
  4 0

To mark records as not active function DBMS_ILM.ARCHIVESTATENAME should be used.

UPDATE test_tbl
SET ora_archive_state=DBMS_ILM.ARCHIVESTATENAME(1)
WHERE ID IN (1,2);

COMMIT;

now only active records are visible in session

SELECT ORA_ARCHIVE_STATE, ID
FROM test_tbl;

ORA_ARCHIVE_STATE           ID
------------------- ----------
0                            3 
0                            4

new session setting is introduced ROW ARCHIVAL VISIBILITY. Following values can be applied for it:

• ALL – allows to see both active and non active records
• ACTIVE – allows to see only active records

ALTER SESSION SET ROW ARCHIVAL VISIBILITY=ALL;

SELECT ORA_ARCHIVE_STATE, ID 
FROM test_tbl;
ORA_ARCHIVE_STATE           ID
------------------- ----------
1                            1 
1                            2 
0                            3 
0                            4

ALTER SESSION SET ROW ARCHIVAL VISIBILITY=ACTIVE;

SELECT ORA_ARCHIVE_STATE, ID
FROM test_tbl;

ORA_ARCHIVE_STATE           ID
------------------- ----------
0                            3 
0                            4

To remove archival visibility just execute ALTER TABLE

ALTER TABLE test_tbl NO ROW ARCHIVAL;

select * from test_tbl;

      ID
--------
       1 
       2 
       3 
       4

Have a fun 🙂

Tomasz