In-Database Archiving — How to archive rows in Oracle 12c
When the organizations migrate the application data from legacy to cloud/modern warehouse systems, legacy data will no longer be required to perform any analysis. In such scenarios the legacy data needs to get archived to avoid the full table scans on the database.
Oracle 12c database has been launched with a new feature called “In-Database Archiving”. This feature helps the users to archive the legacy data without deleting the records from the physical table. In case of any legacy data validation is required in future, the users can still go back to the database and run through the archived data in simple steps.
This is very helpful, when table contains lot of historical data and full scan is taking long time to compute the SQLs.
Let’s run through some example with the table — “wc_archive_test” which has 9 records.
Generate the explain plan -
Run the explain plan to see if full table scan is happening.
- Enable Archive mode on wc_archive_test
2. This will create one invisible column ORA_ARCHIVE_STATE:(0/1)
ORA_ARCHIVE_STATE:0 — Means that row is not archived
ORA_ARCHIVE_STATE:1 — Means that row is archived
3. Let’s try archiving the records with emp_no with 7,8,9
4. Let’s check if the table shows only 6 active records and rest were archived.
5. Now the explain plan should scan through only 6 records.
6. To see the archived records along with the active records –
7. Run the below command to disable the archiving.
select * from wc_archive_test; — should return all the 9 records now.
This will drop the invisible pseudo column ora_archive_state from the table.
