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 12.1.0.2.0 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 12.1.0.2.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options

SQL>

We have a table called school which includes 10 rows

SQL> select count(*) from school;

  COUNT(*)

----------

        10

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;

SQL> COMMIT;

Now the school table has 4 visible rows.

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


         9 Charly        Security   0


        10 Mike          Rman       0

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

alter table school no row archival;