Oracle 12c “In-Database Archiving” Practice
“In-Database Archiving” in Oracle 12c allows particular rows to be marked for deletion instead of physically deleting the rows.
Enable In-Database Archiving:
To enable in-database archiving, simply use ROW ARCHIVAL clause in the CREATE TABLE or ALTER TABLE command.
Create the table with in-database archiving enabled as follows:
SQL> create table test_tab
2 (id number(5),
3 note varchar2(30),
4 constraint pk_test_tab primary key(id)
5 ) ROW ARCHIVAL;
Table created.
A hidden column ORA_ARCHIVE_STATE is generated automatically once
the table with in-database archiving feature is enabled.
SQL> col column_name format a20
SQL> col data_type format a20
SQL> select column_id,column_name,data_type,data_length,hidden_column
2 from user_tab_cols
3 where table_name = 'TEST_TAB'
4 order by column_id;
COLUMN_ID COLUMN_NAME DATA_TYPE DATA_LENGTH HIDDEN_COLUMN
---------- -------------------- -------------------- ----------- --------------------
1 ID NUMBER 22 NO
2 NOTE VARCHAR2 30 NO
ORA_ARCHIVE_STATE VARCHAR2 4000 YES
To Disable In-Database Archiving:
SQL> alter table test_tab NO ROW ARCHIVAL;
Table altered.
To Enable In-Database Archiving:
SQL> alter table test_tab ROW ARCHIVAL;
Table altered.
Populate the table with 100 rows:
SQL> desc test_tab
Name Null? Type
----------------------------------------------------- -------- ----------------
ID NOT NULL NUMBER(5)
NOTE VARCHAR2(30)
SQL> insert into test_tab(id,note)
2 select level,'Description of ' || level
3 from dual
4 connect by level <=100;
100 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) as total_rows from test_tab;
TOTAL_ROWS
-----------------
100
ORA_ARCHIVE_STATE column is populated with the value '0' for each row by default.
SQL> col ORA_ARCHIVE_STATE format a20
SQL> select ORA_ARCHIVE_STATE,count(*) as total
2 from test_tab
3 group by ORA_ARCHIVE_STATE;
ORA_ARCHIVE_STATE TOTAL
-------------------- ----------
0 100
We can discard the rows that are unneeded by updating the ORA_ARCHIVE_STATE column value to any string value other than ‘0’;
SQL> update test_tab set ORA_ARCHIVE_STATE = '1' where id between 51 and 80;
30 rows updated.
SQL> update test_tab set ORA_ARCHIVE_STATE = '2' where id between 81 and 90;
10 rows updated.
SQL> commit;
Commit complete.
SQL> select count(*) from test_tab;
COUNT(*)
----------
60
In order to view the discarded (hidden) rows in the current session, ROW ARCHIVAL VISIBILITY should be set to the value ALL , as follows:
SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;
Session altered.
SQL> select count(*) from test_tab;
COUNT(*)
----------
100
SQL> select ORA_ARCHIVE_STATE,count(*)
2 from test_tab
3 group by ORA_ARCHIVE_STATE
4 order by 1;
ORA_ARCHIVE_STATE COUNT(*)
-------------------- ----------
0 60
1 30
2 10
To View only active rows in the current session by setting the ROW ARCHIVAL VISIBILITY to the value ACTIVE , as follows:
SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;
Session altered.
SQL> select count(*) from test_tab;
COUNT(*)
----------
60
SQL> select ORA_ARCHIVE_STATE,count(*)
2 from test_tab
3 group by ORA_ARCHIVE_STATE;
ORA_ARCHIVE_STATE COUNT(*)
-------------------- ----------
0 60
[Reference]
https://oracle-base.com/articles/12c/in-database-archiving-12cr1
