This tutorial shows how to use in-database archiving to store
all rows in the database, but hide specific rows from
Time to Complete
Approximately 15 minutes
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
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.
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.
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
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
Resetting Your Environment
Perform the following steps to reset your environment prior to repeating the activities covered in this OBE or starting another OBE.
In this tutorial, you have learned how to:
- Enable row archiving for a table.
- Set the session-level parameter to enable as session to view
- Change the archival state of a row.
- Determine whether a row is archived or not.
- Copy rows from a row-archival enabled table.
- To learn more about information lifecycle management refer to additional OBEs in the Oracle Learning Library.
- Lead Curriculum Developer: Dominique Jeunot, Jean-Francois
To help navigate this Oracle by Example, note the following:
- Hiding Header Buttons:
- Click the Title to hide the buttons in the header. To show the buttons again, simply click the Title again.
- Topic List Button:
- A list of all the topics. Click one of the topics to navigate to that section.
- Expand/Collapse All Topics:
- To show/hide all the detail for all the sections. By default, all topics are collapsed
- Show/Hide All Images:
- To show/hide all the screenshots. By default, all images are displayed.
- To print the content. The content currently displayed or hidden will be printed.
To navigate to a particular section in this tutorial, select the topic from the list.