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;

    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

    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.

    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

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

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

    commit;

    Confirm that now only 2 rows are visible via a standard query. Include the ora_archive_state column in the query. The ora_archive_state value for the 2 rows should be 0.

    select employee_id, first_name, ora_archive_state from emp_arch;

Enabling a Session to View Archived Rows

    Set the archival visibility to ALL for the session. This will allow this session to view all rows, archived or not.

    alter session set row archival visibility = all;

    Issue the same query as before, but now you should see all 4 rows.

    select employee_id, first_name, ora_archive_state from emp_arch;

    Set the archival visibility to ACTIVE for the session. This will allow this session to view only active (unarchived) rows.

    alter session set row archival visibility = active;

    Issue the same query as before, but now you should see only the 2 active rows.

    select employee_id, first_name, ora_archive_state from emp_arch;

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;

    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;

    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;

    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;

    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;

    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;

    commit;

    What row archival state do you think the newly inserted rows have? Remember that the archival state is not copied along with the rows.

    Query HR.emp_arch_copy to determine the ora_archive_state value for each row.

    select employee_id, first_name, ora_archive_state from emp_arch_copy;

    You should see that the ora_archive_state values from HR.emp_arch are not propagated to HR.emp_arch_copy. All of the _New rows in HR.emp_arch_copy have ora_archive_state set to the default value of 0 (active). The 2 inactive rows resulted from the update you performed in the HR.emp_arch_copy table in step 7.

Resetting Your Environment

    Perform the following steps to reset your environment prior to repeating the activities covered in this OBE or starting another OBE.

    Drop the tables created in this tutorial.

    drop table emp_arch;

    drop table emp_arch_copy;

Summary

    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 archived rows.
    • Change the archival state of a row.
    • Determine whether a row is archived or not.
    • Copy rows from a row-archival enabled table.

    Resources

    Credits

    • Lead Curriculum Developer: Dominique Jeunot, Jean-Francois Verrier

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.
Print:
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.