Table of Contents

In Database Archiving

Enabling

Can be enabled with CREATE or ALTER TABLE commands … 

CREATE TABLE employees (
  id          NUMBER,
  description VARCHAR2(20)
) ROW ARCHIVAL;

ALTER TABLE employees ROW ARCHIVAL;

This will create a hidden column called ORA_ARCHIVE_STATE.
By default this column will have a value of 0 for each row.
Any value greater than zero makes a SELECT statement ignore this row when ROW ARCHIVAL VISIBILITY is set to ACTIVE.

Disabling

ALTER TABLE employees NO ROW ARCHIVAL;

Displaying Archived Rows

-- Make archived rows visible.
ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;

-- Make archived rows invisible again.
ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;