Oracle Database 12c: In-Database Archiving to deal with logically discarded records
When a record is no longer active – it is logically discarded – you may want to delete it. However, the record may still be required – for compliancy and auditing reasons for example. Or to produce incidental reports. So actually deleting it may not be an option. In the past I have used approaches such as a VALID_YN column or an END_OF_LIFE Date column to mark rows that were really no longer alive. In my applications and using views or VPD policies I can make sure to exclude such rows. That has to be an explicit act and it is a little cumbersome and non-trivial.
Oracle Database 12c introduced the concept of in-database archiving, which is basically what I described overhead – provided by a standard database mechanism that of course the SQL engine knows how to interpret. It works very much like the VALID_YN column – but of course a little differently. The Oracle Documentations says the following: “In-Database Archiving enables you to archive rows within a table by marking them as inactive. These inactive rows are in the database and can be optimized using compression, but are not visible to an application. The data in these rows is available for compliance purposes if needed by setting a session parameter. With In-Database Archiving you can store more data for a longer period of time within a single database, without compromising application performance. Archived data can be compressed to help improve backup performance, and updates to archived data can be deferred during application upgrades to improve the performance of upgrades.” Note the emphasis on ‘without compromising performance’. It would seem that such in line archived records are skipped in a smart way during full table scans and other records retrieval operations. Note that such archived rows do logically still exist: declarative constraints include these rows in their evaluation of uniqueness and referential integrity.
I was going to demonstrate this functionality at length – and then I cam across this excellent write up by Robert Geier: http://blog.contractoracle.com/2013/06/oracle-12c-new-features-in-database-row.html that really says it all in terms of how to get going.
Well, maybe just a little.
To enable in database archiving on a table, you use the clause row archival on the create or an alter statement on the table. This allows you to designate table rows as active or archived. You can then perform queries on only the active rows within the table. When you specify this clause, a hidden column
ORA_ARCHIVE_STATE is created in the table. The column is of data type
VARCHAR2. You can specify a value of
1 for this column to indicate whether a row is active (
0) or archived (
1). If you do not specify a value for
ORA_ARCHIVE_STATE when inserting data into the table, then the value is set to
0 (from the SQL Language reference).
It seems that in how it works out, this features is quite similar to what you could do using VPD (Virtual Private Database): add policy to a table that eliminates all rows from query results that do not have the value 0 in their ORA_ARCHIVE_STATE column. Yet these rows are still part of constraint checking and can also be still queried if the policy has no effect in a certain session. It seems to be part of every database edition – unlike VPD – and it also seems to have a better performance characteristic. I have not investigated that last part though.
I can see a simple migration path for tables that today use a VALID_YN column (that the application knows about) to the standard In-Database Archiving: alter table X row archival; then create a row level update trigger that fires when the VALID_YN is set and set the ORA_ARCHIVE_STATE column accordingly. Depending on whether the session setting
VISIBILITY is at ACTIVE (the default) or ALL – any row that has VALID_YN set to N will also have its ORA_ARCHIVE_STATE set to 1 and is therefore not visible in under default conditions.
A quick demo:
The last two select statements return 107 and 77 rows respectively – indicating the number of archived records.
create table emp as select * from employees desc emp alter table emp row archival; select * from emp select employee_id, last_name , ora_archive_state from emp update emp set ora_archive_state = 1 where employee_id < 130 select employee_id, last_name , ora_archive_state from emp alter session set row archival visibility = active select employee_id, last_name , ora_archive_state from emp
Oracle Database 12c documentation on In Database Archiving: docs.oracle.com/cd/E16655_01/server.121/e17613/part_lifecycle.htm#CHDDAIDE.
SQL Language Reference: http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_7002.htm#SQLRF01402
- PL/SQL Source Code Control inside the database – After Compile trigger for automatic archiving
- OOW 2009: The killer feature of Oracle Database 11gR2 – Edition Based Redefinition (or database object versioning)
- Oracle Open World 2011: Oracle Database 11g Features for Developers by Connor McDonald
- Removing double records (and keeping one of them)
- Have MERGE remove records from Target that are not in the Source – Oracle 10g
- The AMIS Summary of Oracle OpenWorld 2013 is available for download – 60-page white paper
- OOW13 and JavaOne 13: Notes from a Conference – Part Two
- You consolidated your applications in one database, but now one application needs recovery…
- OOW13: Sneak Preview of the Major Announcements?! In-Memory Database, PaaS (Database and Java) and M6 big memory machine
- Het Oracle OpenWorld Preview Evenement (5 september 2013) – 15 sprekers & sessies
- Oracle Database SQL – Recursive Subquery to inspect events in football matches – find the MVP
- Oracle Database 12c: Find most valuable player using MATCH_RECOGNIZE in SQL
- Oracle Database 12c: Flashback Moving Forward
- Oracle Database 12c: quickly create a virtual machine with OEL 6.4 and Oracle Database 12c (for dummies)
- Oracle Database 12c: Pattern Matching through MATCH_RECOGNIZE in SQL