Search notes:

Oracle: In-Database Archiving

Creating a table with the row archival clause …

create table tq84_a (
   id  integer primary key,
   txt varchar2(20)
)
row archival;

… adds an invisible column named ORA_ARCHIVE_STATE:

select
   column_name,
   data_type,
   hidden_column,
   user_generated
from
   user_tab_cols
where
   table_name = 'TQ84_A';
--
-- COLUMN_NAME        DATA_TYPE  HID  USE
-- -----------------  ---------  ---  ---
-- ORA_ARCHIVE_STATE  VARCHAR2   YES  NO 
-- ID                 NUMBER     NO   YES
-- TXT                VARCHAR2   NO   YES

Inserting some records into the table:

begin
   insert into tq84_a values (1, 'one'  );
   insert into tq84_a values (2, 'two'  );
   insert into tq84_a values (3, 'three');
   commit;
end;
/

One record is deleted …

delete from tq84_a where id = 2;

… another set for archival:

update tq84_a
set
   ora_archive_state = '1' -- = dbms_ilm.archiveStateName(dbms_ilm.archive_state_archived)
where
   id = 3;

commit;

I want to see the «active» records only:

alter session set row archival visibility = active;

select * from tq84_a;
--
--         ID TXT                 
-- ---------- --------------------
--          1 one

I want to see the archived records, too:

alter session set row archival visibility = all;

select * from tq84_a;
--
--         ID TXT                 
-- ---------- --------------------
--          1 one                 
--          3 three

Cleaning up:

drop table tq84_a;

Oracle: In-Database Archiving

See also