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';
