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

Introduction

In Oracle Database 12c (12.1.0.1 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.

Demonstration

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:

• CREATE TABLE ... ROW ARCHIVAL clause (Oracle Documentation)
• ALTER SESSION ... ROW ARCHIVAL clause (Oracle Documentation)
• Using In-Database Archiving (Oracle Documentation)