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 and then create a view on that table to display only “active” records.

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;

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

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

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

• 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


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