Sunday, November 15, 2015

12c New feature - Invisible Rows A.K.A In-Database Archiving


In Oracle Database 12c ( to be more specific) Oracle introduced a new feature named "In-Database Archiving" which allows to mark specific rows as invisible (archived) so they will not be visible. For example, if someone queries a table that is configured with this feature enabled then all the rows that are marked as archived will be invisible unless the session has enabled to see archived data. The rows that are archived can be compressed in order to reduce the storage of the database and also improve backup performance.

So how does it work?

In order to configure a table with the In-Database Archiving feature enable, you will need to use the ROW ARCHIVAL clause during the CREATE TABLE command, or if the table already exists then you can use the ROW ARCHIVAL clause using the ALTER TABLE command.
Once you enable this feature, Oracle will create additional column named ORA_ARCHIVE_STATE. By default, this column contains the value '0' for each row which means that the row is visible. If you will change to value to be '1' then the row will be invisible. If you will disable this feature using the ALTER TABLE ... NO ARCHIVAL command then Oracle will automatically drop
this column.


In this demonstration I will perform the following steps:
  1. Create a new table named "test" and enable the ROW Archival feature for this table
  2. Populate the table with 2 rows
  3. Query the table including the ORA_ARCHIVE_STATE column to see that that the additional column contains only the default '0' values:
  4. Mark "David" as invisible by changing the ORA_ARCHIVE_STATE  value to be '1'
  5. Query again the table to verify that "David" is actually invisible
  6. Allow the session to view the invisible data using the ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL command
  7. Query again the table to verify that "David" is now visible
  8. Prohibit the session from viewing the invisible data using the ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE command
  9. Query again the table to verify that "David" is now invisible again
  10. Disable the In-Database Archiving feature for the table using the ALTER TABLE ... NO ARCHIVAL command
  11. Query the table to verify that the ORA_ARCHIVE_STATE column has been dropped

Useful Links:


  1. Just one of those things that might come in handy one day.

    Just wanted to mention that if you want to delete the archived rows, just do:
    SQL> DELETE FROM test WHERE ora_archival_state='1';

    Regards Lasse

    1. Hi Lasse,

      Actually not only '1' represents an archived row. Every string other than '0' represents an archived row (although 1' is the common value to represent an archived row because DBMS_ILM package relies on this constant).

      I would change the delete statement to be:
      DELETE FROM test WHERE ora_archival_state <> '0'

      Thanks for this comment Lasse.