Oracle In Database Archiving
Version 20c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Be sure to view the full listing of monographs in Morgan's Library
Purpose To quote the Oracle New Features doc:

"In-Database Archiving allows users and applications to set the archive state for individual rows. Rows that have been marked as archived will not be visible unless the session is enabled to see archived data. With In-Database Archiving, more data can be stored in production databases for a longer period of time without compromising application performance. In addition, archived data can be aggressively compressed to help improve query and backup performance. Updates to archived data can be deferred during application upgrades, greatly improving the performance of upgrades."

Row-level policies for ADO are not supported for in-database archiving. Partition-level ADO and compression are supported if partitioned on the ORA_ARCHIVE_STATE column.
Dependencies
ALL_TABLES CDB_TAB_COLS USER_TABLES
ALL_TAB_COLS DBA_TABLES USER_TAB_COLS
CDB_TABLES DBA_TAB_COLS  
System Privileges
ALTER SESSION ALTER TABLE CREATE TABLE
 
CREATE
Create and Query CREATE TABLE <normal_create_table_syntax> ROW ARCHIVAL;
conn uwclass/uwclass@pdbdev

CREATE TABLE in_db_arch ROW ARCHIVAL AS
SELECT *
FROM uwclass.servers
WHERE 1=2;

Table created.

desc in_db_arch

Name        Null?    Type
----------- -------- -------------
SRVR_ID              NUMBER(10)
NETWORK_ID           NUMBER(10)
STATUS               VARCHAR2(1)
LATITUDE             FLOAT(20)
LONGITUDE            FLOAT(20)
NETADDRESS           VARCHAR2(15)

col column_name format a17
col data_type format a10

SELECT column_name, data_type, column_id col_id, segment_column_id seg_col_id,
internal_column_id, hidden_column, char_length char_len
FROM user_tab_cols
WHERE table_name = 'IN_DB_ARCH'
ORDER BY 4;

COLUMN_NAME       DATA_TYPE  COL_ID SEGT_COL_ID INTERNAL_COLUMN_ID HID CHAR_LEN
----------------- ---------- ------ ----------- ------------------ --- --------
ORA_ARCHIVE_STATE VARCHAR2                    1           1        YES     4000
SRVR_ID NUMBER                    1           2           2        NO         0
NETWORK_ID NUMBER                 2           3           3        NO         0
STATUS VARCHAR2                   3           4           4        NO         1
LATITUDE FLOAT                    4           5           5        NO         0
LONGITUDE FLOAT                   5           6           6        NO         0
NETADDRESS VARCHAR2               6           7           7        NO        15

INSERT INTO in_db_arch
SELECT * FROM uwclass.servers;

COMMIT;

col ora_archive_state format a18

SELECT srvr_id, ora_archive_state
FROM in_db_arch
ORDER BY 1;

-- set the ORA_ARCHIVE_STATE state to inactive
UPDATE in_db_arch
SET ora_archive_state = '20'
WHERE srvr_id > 600;

SELECT COUNT(*)
FROM in_db_arch;

 COUNT(*)
---------
      126

ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;

Session altered.

SELECT COUNT(*)
FROM in_db_arch;

 COUNT(*)
---------
      141

Session altered.

ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;

Session altered.

SELECT COUNT(*)
FROM in_db_arch;

 COUNT(*)
---------
      126
 
ALTER
In-Database enable a non-enabled table ALTER TABLE <table_name> ROW ARCHIVAL;
CREATE TABLE alter2row_archival AS
SELECT object_name, object_type
FROM user_objects
WHERE rownum < 11;

Table created.

SELECT column_name, hidden_column
FROM user_tab_cols
WHERE table_name = 'ALTER2ROW_ARCHIVAL';

COLUMN_NAME                    HID
------------------------------ ---
OBJECT_NAME                    NO
OBJECT_TYPE                    NO

ALTER TABLE alter2row_archival ROW ARCHIVAL;

Table altered.

SELECT column_name, hidden_column
FROM user_tab_cols
WHERE table_name = 'ALTER2ROW_ARCHIVAL';

COLUMN_NAME                    HID
------------------------------ ---
OBJECT_NAME                    NO
OBJECT_TYPE                    NO
SYS_NC00003$                   YES
ORA_ARCHIVE_STATE              YES

col sys_nc00003$ format a15

SELECT object_name, object_type, sys_nc00003$, ora_archive_state
FROM alter2row_archival;

OBJECT_NAME        OBJECT_TYPE SYS_NC00003$  ORA_ARCHIVE_STATE
------------------ ------------------------- ------------------
ACCESS$            TABLE                     0
ACLMV$             TABLE                     0
ACLMV$_BASE_VIEW   VIEW                      0
ACLMV$_MVINFO      VIEW                      0
ACLMV$_REFLOG      TABLE                     0
ACLMVREFSTAT$      TABLE                     0
ACLMVSUBTBL$       TABLE                     0
ADMINAUTH$         TABLE                     0
ADO_IMCSEQ$        SEQUENCE                  0
ADO_IMPARAM$       TABLE                     0

-- no information on the second hidden column created during the alter table has been published by Oracle to date.
Disable a table previously enabled for ROW ARCHIVAL storage ALTER TABLE <table_name> NO ROW ARCHIVAL;
ALTER TABLE alter2row_archival NO ROW ARCHIVAL;

SELECT column_name, hidden_column
FROM user_tab_cols
WHERE table_name = 'ALTER2ROW_ARCHIVAL';

COLUMN_NAME       HID
----------------- ---
OBJECT_NAME       NO
OBJECT_TYPE       NO
 
Compression
By Enabling IDA in conjunction with partitioning it is theoretically possible to automatically compress partitioned rows. At least that is the theory based on what I've read in the docs ... but as you can see it does not work the way I expected: More research to do. CREATE TABLE list_part (
deptno            NUMBER(10),
deptname          VARCHAR2(20),
quarterly_sales   NUMBER(10,2),
state             VARCHAR2(2))
PARTITION BY LIST (ora_archive_state) (
PARTITION regular VALUES ('0') TABLESPACE uwdata,
PARTITION archive VALUES ('20') TABLESPACE uwdata)
ROW ARCHIVAL;

SELECT column_name, hidden_column
FROM user_tab_cols
WHERE table_name = 'LIST_PART';

COLUMN_NAME       HID
----------------- ---
ORA_ARCHIVE_STATE YES
DEPTNO            NO
DEPTNAME          NO
QUARTERLY_SALES   NO
STATE             NO

INSERT INTO list_part VALUES (1, 'AAA', 10, 'CA');
INSERT INTO list_part VALUES (1, 'BBB', 20, 'OR');
INSERT INTO list_part VALUES (1, 'CCC', 30, 'WA');
COMMIT;

col ora_archive_state format a18

SELECT deptno, deptname, quarterly_sales, state, ora_archive_state
FROM list_part;

 DEPTNO DEPTNAME QUARTERLY_SALES STATE ORA_ARCHIVE_STATE
------- -------- --------------- ----- -- ---------------
      1 AAA                   10 CA       0
      1 BBB                   20 OR       0
      1 CCC                   30 WA       0

SELECT COUNT(*)
FROM list_part PARTITION (regular);

 COUNT(*)
---------
        3

SELECT COUNT(*)
FROM list_part PARTITION (archive);

 COUNT(*)
---------
        0

UPDATE list_part
SET ora_archive_state = 20
WHERE state = 'OR';
 *
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change

Related Topics
Compression
TABLES
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx