Oracle: In-Database Archiving

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

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

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

delete from tq84_a where id = 2;

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

commit;

alter session set row archival visibility = active;

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

alter session set row archival visibility = all;

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

drop table tq84_a;