How to deal with In-database row archiving oracle 12c

Suppose that we have a table of 10 rows in which only the first 5 rows are used / required by the application team. As a DBA, we can decide to keep only the necessary and archive the unnecessary rows. With Oracle’s In-Database archive functionality, it is possible to archive table rows. They will be marked as “invisible” by the application.

Indeed, In-database archive deletes the rows logically but not physically. It allows you to store huge amounts of data over a long period of time without impacting the performance of the application.

Let’s see how to activate In-database row archiving


Let’s connect with a user. Here we will use the user called mikando


[oracle@svrosisdb ~]$ sqlplus mikando/mikando@svrosisdb/tatapdb

SQL*Plus: Release Production on Mon Dec 23 22:09:41 2019

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options




We have a table called school which includes 10 rows


SQL> select count(*) from school;




Enable in-database archiving


alter table school row archival;



In-database row archiving include a hidden column called ora_archive_state.

When ora_archive_state is activated on rows, then it is possible to know if these rows are archived or not.


Let’s check if we have rows archived in the school table.


SQL>  select id, name, subject, ora_archive_state from school;

ID               NAME          SUBJECT           ORA_ARCHIVE_STATE
----------     -------------  ----------       --------------------

1               Kone          Data Guard                   0
2               Paul            VLDB                       0
3               Marc           Exadata                     0
4               Jazz            RAC                        0
5               Lwanga          AVDF                       0
6               Williams        ASM                        0
7               Rowlel         Tuning                      0
8               Firmin          OEM                        0
9               Charly         Security                    0

According to the DBMS_ILM package, 0 means that the line is not activated and 1 means that it is activated.


Let’s archive all the lines with IDs between 3 and 8.

SQL> UPDATE SCHOOL SET    ora_archive_state = '1' WHERE  id BETWEEN 3 and 8;



Now the school table has 4 visible rows.


SQL> select id, name, subject, ora_archive_state from school;


---------- ------------- ---------- --------------------

         1 Kone          Data Guard 0

         2 Paul          VLDB       0

         9 Charly        Security   0

        10 Mike          Rman       0



To redisplay archived rows, simply deactivate the row archival parameter.


alter table school no row archival;




An IT professional with more than 4 years of experience as an Oracle database administrator. Extensive installation experience (RAC and stand-alone), administration, implementation, troubleshooting, Oracle 12c / 11g tuning, export / import, OEM grid control, Data Guard on Unix, Rman backup / restore, upgrade, migration , correction (RDBMS, GRID) PSU & CPU, Database migration. I like everything about the Oracle & DB2 database. For me, sharing is the best thing we can offer in this job. And learning is not an option but a duty for database administrators.

Leave a Reply

Your email address will not be published. Required fields are marked *