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
[[email protected] ~]$ sqlplus mikando/[email protected]/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;
1,334 views last month, 1 views today