In-Database Archiving in Oracle Database - Data Archiving in 12C

In-Database Archiving in Oracle Database 12c - Data Archiving in 12C

Rather than deleting data physically, some applications have a concept of "mark for delete" logical delete, so the data remains present in the table, but is not visible to the application. This can be 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, like in Virtual Private Database (VPD).

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
Archiving (Deleting) Rows
Displaying Archived Rows


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 ARCH_TEST PURGE;

-- Create the table with in-database with archiving of data enabled.

CREATE TABLE ARCH_TEST (
  id          NUMBER,
  description VARCHAR2(50),
  CONSTRAINT ARCH_TEST_pk PRIMARY KEY (id)
)
ROW ARCHIVAL;

-- Insert data into TEST TABLE ARCH_TEST

declare
n number;
VSQL Varchar(2000);
--i number:=1;
begin
for n in  1..750
loop
-- n := n+1;
VSQL:='insert into ARCH_TEST (id ,DESCRIPTION) values ('||n||',''VALUE OF N IS '||n||''')';
-- DBMS_OUTPUT.PUT_LINE(VSQL);
Execute Immediate VSQL;

end loop;
commit;
end;
/

-- Check the contents of the table.

SELECT COUNT(*) FROM ARCH_TEST;

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

 
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 = 'ARCH_TEST'
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   ARCH_TEST
GROUP BY ora_archive_state
ORDER BY ora_archive_state;   
  
ORA_ARCHIVE_STATE      COUNT(*)
-------------------- ----------
0                           750  
  

-- Disable, the re-enable in-database archiving.

ALTER TABLE ARCH_TEST NO ROW ARCHIVAL;
ALTER TABLE ARCH_TEST ROW ARCHIVAL;

************************************************************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 ARCH_TEST
SET    ora_archive_state = '1'
WHERE  id BETWEEN 550 and 750;
COMMIT;

SELECT COUNT(*) FROM ARCH_TEST;

  COUNT(*)
----------
       549

  
We 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'

SQL>

************************************************************
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 ARCH_TEST;

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

SQL>


COLUMN ora_archive_state FORMAT A20

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

ORA_ARCHIVE_STATE      COUNT(*)
-------------------- ----------
0                           549
1                           201

2 rows selected.

SQL>

-- Make archived rows invisible again.

ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;

SELECT COUNT(*) FROM ARCH_TEST;

 COUNT(*)
----------
       549

SQL>