Using In-Database Row Archiving

Overview

Purpose

This tutorial shows how to use in-database archiving to store all rows in the database, but hide specific rows from applications.

Time to Complete

Approximately 15 minutes

Introduction

In-database archiving enables you to archive rows within a table by marking them as invisible. These invisible rows are in the database and are 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, manipulate the ORA_ARCHIVE_STATE hidden column of the table, and specify either ACTIVE or ALL for the ROW ARCHIVAL VISIBILITY session parameter.

Scenario

In this tutorial, you first create a copy of the HR.EMPLOYEES table and enable row archival on the copy. Next, you flag the archived rows in the archival enabled table. You then query the table to verify that the archived rows are not displayed unless you enable the rows to be visible for the session.

Prerequisites

Before starting this tutorial, you should:

Oracle Database 12c should be installed.
You need a container database with a pluggable database which has the HR sample schema.

The environment used in the development of this tutorial was as follows:

ORACLE_HOME: /u01/app/oracle/product/12.1.0
TNS Listener port: 1521
Non-container database info:
- SID: noncdb
Container database info:
- SID: cdb1, with pluggable databases pdb1, and pdb2
- SID: cdb2, with no pluggable databases

Creating a Table and Enabling Row Archival

Connect to pdb1 as the HR user.

. oraenv
[Enter cdb1 at the prompt.]

sqlplus hr/hr@localhost:1521/pdb1

Create a copy of the HR.employees table. Call the copy HR.emp_arch, and only copy over 4 rows.

create table emp_arch
as select employee_id, first_name from employees where rownum <= 4;

Enable row archival on HR.emp_arch.

alter table emp_arch row archival;

images/t10101.jpg — There is a hidden column in row-archival-enabled tables called ora_archive_state which indicates whether a row is archived or not. The hidden column is only displayed if specified in a query. First, describe the table structure of HR.emp_arch. Notice that the ora_archive_state column is not listed.

desc emp_arch

images/t10102.jpg — Now, query the HR.emp_arch table. Display the ora_archive_state column in the query result.

select employee_id, first_name, ora_archive_state from emp_arch;

A value of 0 in the ora_archive_state column means the row is not archived; in other words, the row is active and visible via a standard query.

images/t10103.jpg — Confirm that all rows are active by issuing a standard query. You should see all 4 rows.

select * from emp_arch;

Setting the Archive State of Some Rows

Enabling a Session to View Archived Rows

Verifying that Copying a Row-Archival Table Will Not Propagate the Source Table's Archival State to the Target Table

Use CTAS to make a copy of the HR.emp_arch table.

create table emp_arch_copy as select employee_id, first_name from emp_arch;

images/t40101.jpg — Verify that HR.emp_arch_copy does not have row archival enabled, and therefore does not contain the ora_archive_state hidden column. You should see an "invalid identifier" error for this query.

select employee_id, first_name, ora_archive_state from emp_arch_copy;

images/t40102.jpg — Query the table again, but this time omit the hidden column. Notice that all 4 rows are copied even though you set the row archival visibility for this session to active rows only.

select employee_id, first_name from emp_arch_copy;

Set the archival visibility to ALL for the session. From this point forward, this session should now see all rows, whether archived or not.

alter session set row archival visibility = all;

Enable row archival for HR.emp_arch_copy.

alter table emp_arch_copy row archival;

images/t40103.jpg — Verify that HR.emp_arch_copy now has row archival enabled, and therefore contains the ora_archive_state hidden column.

select employee_id, first_name, ora_archive_state from emp_arch_copy;

Use the dbms_ilm.archivestatename procedure to update the ora_archive_state value for employee_id 102 and 103.

update emp_arch_copy
set ora_archive_state=dbms_ilm.archivestatename(1)
where employee_id in (102, 103);

commit;

images/t40104.jpg — Confirm that now 2 rows are active, and 2 rows are inactive, via a standard query. Include the ora_archive_state column in the query. The ora_archive_state value for the 2 active rows should be 0, and the 2 inactive rows should be 1.

select employee_id, first_name, ora_archive_state from emp_arch_copy;

images/t40105.jpg — Recall that the HR.emp_arch table you created earlier contains 2 active and 2 inactive rows. Confirm this via a standard query. Include the ora_archive_state column in the query. The ora_archive_state value for the 2 active rows should be 0, and the 2 inactive rows should be 1.

select employee_id, first_name, ora_archive_state from emp_arch;

Copy all rows from HR.emp_arch to HR.emp_arch_copy. To distinguish between rows that were previously copied to HR.emp_arch_copy via CTAS, and the rows being copied again now, append '_New' to the values in the first_name column values.

insert into emp_arch_copy select employee_id, first_name || '_New' from emp_arch;