ora_archive_state Tips

 Starting in Oracle 12c, Oracle provides a new feature that allow you to "logically delete" a row in a table without physically removing the row.  This effectively makes deleted rows "invisible" to all SQL and DML, but they can be revealed at any time, providing a sort of "instant" rollback method.

 

To use ora_archive_state as an alternative to deleting rows, you need the following settings and parameters:

 

1. Create the table with the row archival clause

 

      create table mytab (col1 number, col2 char(200)) row archival;

 

2.  Now that the table is marked as row archival, you have two methods for removing rows, a permanent solution with the standard delete DML, plus the new syntax where you set ora_archive_state to a non-zero value:

 

      update mytab set ora_archive_state=2 where col2='FRED';  

 

3.  To make "invisible rows" visible again, you simply set the rows ora_archive_state to zero:

 

      update mytab set ora_archive_state=0 where col2='FRED';  

 

To see rows that are marked as deleted you can select the pseudo-column ora_archive_state from a table, just as-if it were a real table column.  This is a binary pseudo-column, and a "1" value indicates a logically deleted table row.

 

   select id, col1, col2, ora_archive_state from mytab;

Removing row invisibility

Below we see a case where we set the row archival visibility parameter to "all" thereby allowing us to see all of the rows that have been logically deleted:

   alter session set row archival visibility = all;

   select col1, col2, ora_archive_state from mytab;

We can then turn-on row invisibility back on by changing row archival visibility = "active":

   alter session set row archival visibility = all;

We also see two new data dictionary columns in dba_tab_cols, hidden_column (an indicator) and user_generated:

select
   table_name,
   column_name,
   hidden_column,
   user_generated
from
   dba_tab_cols
where
   table_name='MYTAB';