🔍 What is In-Database Archiving (IDA)?
In-Database Archiving (IDA) is a feature introduced in Oracle 12c that allows you to logically archive data within the same table, rather than moving it to a separate archive table or physically deleting it.
It uses a hidden column called ORA_ARCHIVE_STATE, which is automatically managed by Oracle once row archival is enabled on a table.
❓ Why is IDA required?
✅ Business Scenarios:
When deletion is not allowed due to compliance, audit, or legal reasons.
You want to exclude inactive/obsolete records from day-to-day operations but still retain them in the same table.
Simplifies data access — no need to join between live and archive tables.
🚫 What happens if you don’t use IDA?
You’ll need to create separate archive tables.
Requires additional code to move data, and manage archival logic.
Queries become more complex: either UNION with archive or write custom views.
Risk of accidental deletion or loss of referential integrity.
✅ What happens if you use IDA?
You retain historical/inactive rows safely.
Archived rows are hidden from normal queries unless explicitly requested.
You reduce table bloat visibly, while physically keeping data.
Cleaner code: your business logic need not filter is_active = 'N' manually.
💡 How it works — Step-by-step with example
Let's go through a full working example with explanation.
🔧 Step 1: Create a sample table
CREATE TABLE employees (
emp_id NUMBER,
emp_name VARCHAR2(100),
hire_date DATE,
status VARCHAR2(10)
);
🔧 Step 2: Enable In-Database Archiving
ALTER TABLE employees ROW ARCHIVAL;
✅ This adds an internal column called ORA_ARCHIVE_STATE (hidden by default).
🔧 Step 3: Insert some data
INSERT INTO employees VALUES (1, 'Pranav', TO_DATE('01-JAN-2019', 'DD-MON-YYYY'), 'Active');
INSERT INTO employees VALUES (2, 'Sohan', TO_DATE('01-JAN-2015', 'DD-MON-YYYY'), 'Inactive');
COMMIT;
By default, all inserted rows get ORA_ARCHIVE_STATE = 0 (active).
🔧 Step 4: Archive an old row logically
UPDATE employees
SET ORA_ARCHIVE_STATE = 1
WHERE emp_id = 2;
COMMIT;
🔒 The row is now logically archived.
🔍 Step 5: Querying the table
✅ Default behavior (returns only active data):
SELECT * FROM employees;
➡️ Only returns Pranav’s row (ORA_ARCHIVE_STATE = 0).
👀 If you want to see all rows including archived:
SELECT * FROM employees NO_ARCHIVE;
➡️ Returns both Pranav and Sohan.
✅ You can also filter manually:
SELECT * FROM employees WHERE ORA_ARCHIVE_STATE = 1;
➡️ Returns only Sohan (archived).
🧠 Key Internals & Behavior
ORA_ARCHIVE_STATE is a hidden column (invisible unless queried explicitly).
You don’t need to modify your existing application queries — they will automatically ignore archived rows.
No performance impact on live data access — Oracle manages it smartly.
Supports indexing, constraints, and backups as usual.
📌 Best Practices
Tip Description
Use for soft-delete Avoid physical deletes if compliance mandates data retention.
Index as needed You can still index other columns; ORA_ARCHIVE_STATE is auto-managed.
Use in reports Archived rows can be fetched for audit/logs without restoring data.
Combine with virtual columns If you need additional flags like “archival_reason”.
✅ Summary Statement (For Resume or Interview)
Implemented Oracle In-Database Archiving (IDA) to logically archive historical records within operational tables, using the hidden ORA_ARCHIVE_STATE column. This approach enabled data retention without physical deletion, ensuring regulatory compliance, simplified query logic, and auditability with minimal performance overhead.
Top comments (0)