8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

In-Database Archiving in Oracle Database 12c Release 1 (12.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.

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.

Related articles.

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.

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>

Unarchiving Rows

If you need to do this, you should probably question why you made such a poor decision in archiving the rows in the first place.

You can unarchive rows by resetting the ORA_ARCHIVE_STATE column value to '0' for the archived rows. The archived rows must be visible in your session before you do this.

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

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

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

SELECT COUNT(*) FROM tab1;

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

SQL>

Thanks for Koen Lostrie in the comments for suggesting this addition.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.