All about Database Administration, Tips & Tricks

An direct example from documentation with clear explanation

In-Database Archiving enables you to archive rows within a table by marking them as inactive. These inactive rows are in the database and can be optimized using compression, but are not visible to an application. The data in these rows is available for compliance purposes if needed by setting a session parameter.

With In-Database Archiving you can store more data for a longer period of time within a single database, without compromising application performance. Archived data can be compressed to help improve backup performance, and updates to archived data can be deferred during application upgrades to improve the performance of upgrades.

To manage In-Database Archiving for a table, you must enable ROW ARCHIVAL for the table and manipulate the ORA_ARCHIVE_STATE hidden column of the table. Optionally, you specify either ACTIVE or ALL for the ROW ARCHIVAL VISIBILITY session parameter.

	ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;

	CREATE TABLE employees_indbarch 
	 (employee_id NUMBER(6) NOT NULL, 
	  first_name VARCHAR2(20), last_name VARCHAR2(25) NOT NULL, 
	  email VARCHAR2(25) NOT NULL, phone_number VARCHAR2(20), 
	  hire_date DATE NOT NULL, job_id VARCHAR2(10) NOT NULL, salary  NUMBER(8,2),
	  commission_pct NUMBER(2,2), manager_id NUMBER(6), department_id NUMBER(4)) ROW ARCHIVAL;

	INSERT INTO employees_indbarch(employee_id, first_name, last_name, email,
	  hire_date, job_id, salary, manager_id, department_id) 
	  VALUES (251, 'Scott', 'Tiger', 'scott.tiger@example.com', '21-MAY-2009',
	 'IT_PROG', 50000, 103, 60);

	INSERT INTO employees_indbarch(employee_id, first_name, last_name, email,
	  hire_date, job_id, salary, manager_id, department_id) 
	  VALUES (252, 'Jane', 'Lion', 'jane.lion@example.com', '11-JUN-2009', 
	  'IT_PROG', 50000, 103, 60);

	SELECT SUBSTR(COLUMN_NAME,1,22), SUBSTR(DATA_TYPE,1,20), COLUMN_ID AS COL_ID,
	  SEGMENT_COLUMN_ID AS SEG_COL_ID, INTERNAL_COLUMN_ID AS INT_COL_ID, HIDDEN_COLUMN, CHAR_LENGTH
	  FROM USER_TAB_COLS WHERE TABLE_NAME='EMPLOYEES_INDBARCH';

	SELECT SUBSTR(COLUMN_NAME,1,22) NAME, SUBSTR(DATA_TYPE,1,20) DATA_TYPE, COLUMN_ID AS COL_ID,
	  SEGMENT_COLUMN_ID AS SEG_COL_ID, INTERNAL_COLUMN_ID AS INT_COL_ID, HIDDEN_COLUMN, CHAR_LENGTH 
	  FROM USER_TAB_COLS WHERE TABLE_NAME='EMPLOYEES_INDBARCH';

	NAME                   DATA_TYPE                COL_ID SEG_COL_ID INT_COL_ID HID CHAR_LENGTH
	---------------------- -------------------- ---------- ---------- ---------- --- -----------
	ORA_ARCHIVE_STATE      VARCHAR2                                 1          1 YES        4000
	EMPLOYEE_ID            NUMBER                        1          2          2 NO            0
	FIRST_NAME             VARCHAR2                      2          3          3 NO           20
	LAST_NAME              VARCHAR2                      3          4          4 NO           25
	EMAIL                  VARCHAR2                      4          5          5 NO           25
	PHONE_NUMBER           VARCHAR2                      5          6          6 NO           20
	HIRE_DATE              DATE                          6          7          7 NO            0
	JOB_ID                 VARCHAR2                      7          8          8 NO           10
	SALARY                 NUMBER                        8          9          9 NO            0
	COMMISSION_PCT         NUMBER                        9         10         10 NO            0
	MANAGER_ID             NUMBER                       10         11         11 NO            0
	DEPARTMENT_ID          NUMBER                       11         12         12 NO            0


	COLUMN ORA_ARCHIVE_STATE FORMAT a18;
	/* The default value for ORA_ARCHIVE_STATE is '0', which means active */

	SELECT employee_id, ORA_ARCHIVE_STATE FROM employees_indbarch;

	EMPLOYEE_ID ORA_ARCHIVE_STATE
	----------- ------------------
		251 0
		252 0

	/* Update a value into ORA_ARCHIVE_STATE to set inactive , updating any value other than 0 makes the row visibility archival inactive*/

	UPDATE employees_indbarch SET ORA_ARCHIVE_STATE = '20' WHERE employee_id = 252;

	/* Only active records are in the following query, you see that 252 id is not visible since the visibility is not set to all */

	SELECT employee_id, ORA_ARCHIVE_STATE FROM employees_indbarch;

	EMPLOYEE_ID ORA_ARCHIVE_STATE
	----------- ------------------
		251 0

	/* Set visibility to ALL to display all records */

	ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;

	SELECT employee_id, ORA_ARCHIVE_STATE FROM employees_indbarch;

	EMPLOYEE_ID ORA_ARCHIVE_STATE
	----------- ------------------
		251 0
		252 20

As you see the setting the row archival visibility to all displays all records. this is especially useful to filter out the historical data