Oracle Temporal Validity
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
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;

desc in_db_arch

col column_name format a17
col data_type format a10

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

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 srvr_id, ora_archive_state
FROM in_db_arch
ORDER BY 1;

ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;

SELECT srvr_id, ora_archive_state
FROM in_db_arch
ORDER BY 1;

ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;

SELECT srvr_id, ora_archive_state
FROM in_db_arch;
 
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;

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;

-- 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 table alter2row_archival NO ROW ARCHIVAL;

SELECT column_name, hidden_column
FROM user_tab_cols
WHERE table_name = 'ALTER2ROW_ARCHIVAL';
 
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';

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;

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

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

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

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-2014 Daniel A. Morgan All Rights Reserved