In-database archiving enables you to archive rows within a table by marking them as invisible. Rows archived makes them invisible are in the database and they are compressed automatically. The invisible rows are not visible by the application and are not backup by RMAN. We can store these more data for longer period of time
when ever you are creating table with row archival a new hidden column “ORA_ARCHIVE_STATE” is added to it. This column is not generated by user. it is created by oracle and maintained itself.
Creating a table with row archival
Create table world (id number, name varchar(10), last varchar(10)) row archival;
when ever new row is inserted "By default, the rows inserted is in active state and the ORA_ARCHIVE_STATE value is ‘0’. If we want to disable the rows (or) enable archiving change the value of column "ORA_ARCHIVE_STATE" to non zero value, 1 or 2 disables row or enable archiving on that table. example as follows
Insert some new data into the table
insert into world values ('1','Nikhil','Tatineni’);
insert into world values ('2','Terry’,’Loving');
commit;
SQL> select id, name,last, ora_archive_state from world;
ID NAME LAST ORA_ARCHIVE_STATE
---------- ---------- ---------- --------------------
1 Nikhil Tatineni 0
2 Terry Loving 0
col hidden for a7
col TABLE_NAME a20
col HIDDEN_COLUMN for a20
col USER_GENERATED for a20
select TABLE_NAME, COLUMN_NAME, HIDDEN_COLUMN, USER_GENERATED
from user_tab_cols where table_name='WORLD';
"ORA_ARCHIVE_STATE'' is hidden column
Changing the value for hidden column to non zero value will disabling rows
SQL> update world set ora_archive_state=dbms_ilm.archivestatename(1) where id in (1, 2);
2 rows updated.
(or)
SQL> update world set ora_archive_state=2 where id=2;
1 row updated.
SQL> update world set ora_archive_state=2 where id=1;
1 row updated.
Enable session and application level to view archived rows in world table
alter session set row archival visibility = all;
SQL> select ID,NAME,LAST,ORA_ARCHIVE_STATE from world;
ID NAME LAST ORA_ARCHIVE_STATE
---------- ---------- ---------- --------------------
1 Nikhil Tatineni 2
2 Terry Loving 1
Enable session and application level to view non archived rows in world table
SQL> alter session set row archival visibility = active;
Session altered.
SQL> select ID,NAME,LAST,ORA_ARCHIVE_STATE from world;
no rows selected
How to disable archiving for this table ?
SQL> alter table world no row archival;
when you give this command automatically, "ORA_ARCHIVE_STATE" hidden column will be dropped from the world table
for more info, please go through following link
http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/ilm/row_archival/row_archival.html
-----Nikhil Tatineni
-----12c-- In database Archiving ---
