Oracle 12c supports in-database archiving to compress data that is no longer readily needed but is still kept online. This article covers the rules of in-Database archiving with a brief example.
Use In-Database archiving rules–
- Allows rows to be kept in the database but invisible from applications.
- Idea is the data is available for compliance but minimize impact to the performance.
- Archive data can be compressed to help improve backup performance.
- The column clause ROW ARCHIVAL of the table must be enabled and the ORA_ARCHIVE_STATE must be set to non-zero value.
- The session parameter ROW ARCHIVAL VISIBILITY is set to ACTIVE, only rows where ORA_ARCHIVE_STATE equals zero will be displayed.
- The session parameter ROW_ARCHIVAL VISIBILITY is set to ALL, all rows will be displayed.
In-Database archiving Eample.
- Logon to sqlplus as sysdba.
[oracle@linux2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 15 09:10:34 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>
- Alter session to set row archival visibility to active.
ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;
SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;
Session altered.
SQL>
- Create table test.archival_test with following statement including ROW ARCHIVAL clause. This creates a hidden column call ORA_ARCHIVE_STATE which determines the archival status of the row.
CREATE TABLE test.archival_test (col1 NUMBER, col2 VARCHAR2( 20)) ROW ARCHIVAL;
SQL> CREATE TABLE test.archival_test (col1 NUMBER, col2 VARCHAR2( 20)) ROW ARCHIVAL;
Table created.
SQL>
- Insert records into test.archival_test table.
INSERT INTO test.archival_test (col1, col2) VALUES (1, ‘Record One’);
INSERT INTO test.archival_test (col1, col2) VALUES (2, ‘Record Two’);
INSERT INTO test.archival_test (col1, col2) VALUES (3, ‘Record Three’);
INSERT INTO test.archival_test (col1, col2) VALUES (4, ‘Record Four’);
- Select values in test.archival_test table to determine ora_archive_state column value.
column col1 format 9999
column col2 format a12
column ora_archive_state format a15
SQL> column col1 format 9999
column col2 format a12
column ora_archive_state format a15
SELECT col1, col2, ora_archive_state FROM test.archival_test;
SQL> SQL> SQL>
COL1 COL2 ORA_ARCHIVE_STA
—– ———— —————
1 Record One 0
2 Record Two 0
3 Record Three 0
4 Record Four 0
SQL>
- Update the column ora_archive_state from 0 to 5 for record 3.
UPDATE test.archival_test SET ora_archive_state = ‘5’ WHERE col1 = 3;
SQL> UPDATE test.archival_test SET ora_archive_state = ‘5’ WHERE col1 = 3;
1 row updated.
SQL>
- Perform another select of the table ora_archive_state and note that record three does not appear in result set.
SQL> SELECT col1, col2, ora_archive_state FROM test.archival_test;
COL1 COL2 ORA_ARCHIVE_STA
—– ———— —————
1 Record One 0
2 Record Two 0
4 Record Four 0
SQL>
- Set the ROW ARCHIVAL VISIBILITY to all.
ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;
SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;
Session altered.
SQL>
- Now slect all values in archival_test and not that all values are displayed.
SELECT * FROM test.archival_test;
SQL> SELECT * FROM test.archival_test;
COL1 COL2
—– ————
1 Record One
2 Record Two
3 Record Three
4 Record Four
SQL>
- This completes the example of In-Database archiving in Oracle 12c.
Larry Catt
OCP