8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | 24ai | 26ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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.
- 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.
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.
- 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>
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...