In-database archiving of Oracle 12c new features (In-database archiving)

The new features of In-database archiving are introduced in Oracle Database 12c, which allows users to archive data by marking data rows on the table as inactive inactive. These inactive data rows can be compressed into one optimization and are not visible to the app by default. This feature enables the functionality and requirements of this "tag delete" in the case of minimal changes to existing code.

Before 12c:
Some applications have the concept of "tag delete", that is, instead of deleting data, the data remains in the table and is not visible to the app. This requirement is usually achieved by:
1) Add an additional column to the related table that stores the flags that flag data is deleted.
2) Add a predicate to each statement to check the state of the deleted row, like: "WHERE deleted = ' N '" to exclude deleted rows. Predicates can be hard-coded into SQL statements, or dynamically apply security policies similar to VPD.

After 12c:
1. Open the In-database archive
The ROW archival clause is used to open the in-database archive. This clause can be used when creating tables in create TABLE, or in ALTER TABLE after the tables have been created.

--Create a table and open the in-database archive feature
Sql>grant Select on Dba_objects to C # #andy Container=all;
Sql> Conn C # #andy/andy
Connected.
Sql>create table Ida as select object_name,object_id from dba_objects where 1=2;
Sql>insert into Ida Select object_name,object_id from Dba_objects ORDER by OBJECT_ID fetch first ten percent rows only;
7281 rows created.
sql> ALTER TABLE Ida ROW ARCHIVAL; or specify the CREATE TABLE (XXX) Rowarchival when building the tables;
Table altered.
--Check the contents of the table
Sql>select COUNT (*) from Ida;
COUNT (*)
----------
7281
Sql> desc Ida;
Name Null? Type
----------------------------------------- -------- ----------------------------
object_name VARCHAR2 (128)
OBJECT_ID number

Opening the In-database archive feature will cause the table to add another hidden column called the "Ora_archive_state" system.
Sql>
COL column_name FORMAT A20;
COL data_type FORMAT A20;
SELECT column_id,
COLUMN_NAME,
Data_type,
Data_length,
Hidden_column
From User_tab_cols
WHERE table_name = ' IDA '
ORDER by column_id;
COLUMN_ID column_name data_type data_length HID
---------- -------------------- -------------------- ----------- ---
1 object_name VARCHAR2
2 object_id Number NO
sys_nc00003$ RAW 126 YES
ORA_ARCHIVE_STATEVARCHAR2 4000 YES

By default, each row of the column is populated with ' 0 '.
Sql>
COL ora_archive_state FORMAT A20;
SELECT Ora_archive_state,count (*)
From Ida
GROUP by Ora_archive_state
ORDER by Ora_archive_state;
Ora_archive_state COUNT (*)
-------------------- ----------
0 7281

2. Archived (Deleted) rows
Instead of deleting unwanted rows, change the value of the Ora_archive_state system hidden column to ' 1 '. This will cause the app not to see these lines.
Sql>
UPDATE Ida
SET ora_archive_state = ' 1 '
WHERE object_id>7000;
284 rows updated.
sql>commit;

Sql>select COUNT (*) from Ida;
COUNT (*)
----------
6997 > Hidden columns already appear

3. Show Archive lines
By setting Row_archival_visibility to all, you can make these hidden rows visible to the session. Setting this parameter back to active can make these rows invisible again.
--Make the archived rows visible
Sql>alter SESSION SET ROW ARCHIVAL VISIBILITY = all;
Sql>select COUNT (*) from Ida;
COUNT (*)
----------

7281 > Hidden rows visible to a session

--Make the archive row invisible again
Sql>alter SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;

Sql>select COUNT (*) from Ida;
COUNT (*)
----------
6997 > Hidden rows are not visible to the session

--Disabling and re-opening the in-database archive feature
Sql>alter TABLE Ida NO ROW ARCHIVAL;
Sql>alter TABLE Ida ROW ARCHIVAL;

In-database archiving of Oracle 12c new features (In-database archiving)