Tuesday, 1 August 2017

In-Database Archiving in Oracle Database 12c Release 1

Rather than deleting data, some applications have a concept of "mark for delete", so the data remains present in the table, but is not visible to the application. This is usually achieved by doing the following.
  • Add an extra column to the relevant tables that holds a flag to indicate the data is deleted.
  • Add an extra predicate to every statement that checks the deleted status, like "WHERE deleted = 'N'", to exclude the deleted rows from the SQL. The predicate can be hard coded into the SQL, or applied dynamically using a security policy.
In-Database Archiving is a feature added to Oracle Database 12c to allow this type of "mark for delete" functionality out-of-the-box, with fewer changes to the existing application code.

◈ Enable In-Database Archiving


The ROW ARCHIVAL clause is used to enable in-database archiving. It can be used during table creation as part of the CREATE TABLE command, or after table creation using the ALTER TABLE command.

DROP TABLE tab1 PURGE;

-- Create the table with in-database archiving enabled.
CREATE TABLE tab1 (
  id          NUMBER,
  description VARCHAR2(50),
  CONSTRAINT tab1_pk PRIMARY KEY (id)
)
ROW ARCHIVAL;

-- Disable, the re-enable in-database archiving.
ALTER TABLE tab1 NO ROW ARCHIVAL;
ALTER TABLE tab1 ROW ARCHIVAL;

-- Populate the table with 1000 rows.
INSERT /*+ APPEND */ INTO tab1
SELECT level, 'Description of ' || level
FROM   dual
CONNECT BY level <= 1000;

COMMIT;

-- Check the contents of the table.
SELECT COUNT(*) FROM tab1;

COUNT(*)
----------
   1000

SQL>

Enabling in-database archiving on the table results in the addition of a system generated hidden column called ORA_ARCHIVE_STATE.

COLUMN column_name FORMAT A20
COLUMN data_type FORMAT A20

SELECT column_id,
       column_name,
       data_type,
       data_length,
       hidden_column
FROM   user_tab_cols
WHERE  table_name = 'TAB1'
ORDER BY column_id;

COLUMN_ID   COLUMN_NAME DATA_TYPE DATA_LENGTH     HID
---------- --------------------  -------------------- -----------   ---
    1           ID NUMBER 22     NO
    2           DESCRIPTION VARCHAR2 50     NO
                ORA_ARCHIVE_STATE     VARCHAR2 4000  YES

SQL>

By default, this column is populated with the value '0' for each row.

COLUMN ora_archive_state FORMAT A20

SELECT ora_archive_state, COUNT(*)
FROM   tab1
GROUP BY ora_archive_state
ORDER BY ora_archive_state;

ORA_ARCHIVE_STATE      COUNT(*)
-------------------- ----------
0                          1000

1 row selected.

SQL>

◈ Archiving (Deleting) Rows


Rather than deleting unneeded rows, update the ORA_ARCHIVE_STATE system generated hidden column with the value '1'. This will make the rows invisible to your applications.

UPDATE tab1
SET    ora_archive_state = '1'
WHERE  id BETWEEN 751 and 1000;
COMMIT;

SELECT COUNT(*) FROM tab1;

COUNT(*)
----------
    750

SQL>

You can actually set ORA_ARCHIVE_STATE column to any string value other than '0' to archive the data, but the DBMS_ILM package uses the following constants.
  • ARCHIVE_STATE_ACTIVE='0'
  • ARCHIVE_STATE_ARCHIVED='1'

◈ Displaying Archived Rows


The hidden rows can be made visible to a session by setting ROW ARCHIVAL VISIBILITY to the value ALL. Setting it back to ACTIVE makes the rows invisible again.

-- Make archived rows visible.
ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;

SELECT COUNT(*) FROM tab1;

 COUNT(*)
----------
   1000

SQL>

COLUMN ora_archive_state FORMAT A20

SELECT ora_archive_state, COUNT(*)
FROM   tab1
GROUP BY ora_archive_state
ORDER BY ora_archive_state;

ORA_ARCHIVE_STATE      COUNT(*)
-------------------- ----------
0                           750
1                           250

2 rows selected.

SQL>

-- Make archived rows invisible again.
ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;

SELECT COUNT(*) FROM tab1;

 COUNT(*)
----------
    750

SQL>