Oracle 12c database Archive

Oracle 12c has a new feature that can archive data in the database, hide infrequently accessed data from the application, and set it to inactive. Of course, it can also be displayed at the session level. This is achieved through the row archival visibility parameter, which can be set to two values, active or all. Active is the data that shows the activity, and all is the data that shows all, including inactive data.

Examples:

SQL> create table test (id int, name varchar2 (10 ));
Table created.
SQL> alter table test ROW ARCHIVAL;
Table altered.
SQL> insert into test values (1, 'col1 ');
1 row created.
SQL> insert into test values (2, 'col2 ');
1 row created.
SQL> commit;

SQL> COL DATA_TYPE FOR A10
SQL> COL COLUMN_NAME FOR A20

SQL> SELECT COLUMN_NAME, DATA_TYPE, HIDDEN_COLUMN, CHAR_LENGTH FROM USER_TAB_COLS WHERE TABLE_NAME = 'test ';

COLUMN_NAME DATA_TYPE HID CHAR_LENGTH
--------------------------------------------
ORA_ARCHIVE_STATE VARCHAR2 YES 4000
NAME VARCHAR2 NO 10
Id number no 0

SQL> col ora_archive_state for a20

SQL> select id, name, ora_archive_state from test;

 

Id name ORA_ARCHIVE_STATE
----------------------------------------
1 col1 0
2 col2 0

 

By default, the value of the ora_archive_state column is 0, which means it is not archived in the database.

SQL> update test set ora_archive_state = '1' where id = 1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from test;

ID NAME
--------------------
2 col2
We can see that the modified data is no longer visible.

SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;

Session altered.

SQL> select * from test;

ID NAME
--------------------
1 col1
2 col2
SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = active;

Session altered.

SQL> select * from test;

ID NAME
--------------------
2 col2

We can see that different session parameters have different display results.