ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
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.
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;
-- 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.
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
