Why Do We Need Immutable Tables?
In many applications, certain data should never be modified once it is stored. Traditional tables allow UPDATE and DELETE operations, which can accidentally or intentionally change critical records.
Immutable tables solve this problem by allowing only INSERT operations and protecting data from modification or deletion until the configured retention period expires.
Common use cases include:
- Audit logs
- Financial transactions
- Compliance records (SOX, HIPAA, GDPR)
- Healthcare data
- Legal and government records
Immutable Tables in Oracle Database
Immutable tables are insert-only relational structures that enforce immutability through system-managed retention controls at both the table and row levels. These controls prohibit UPDATE and DELETE operations until the retention period expires.
While functionally similar to blockchain tables in terms of data immutability, immutable tables do not maintain cryptographic hash links between records.
Oracle introduced immutable tables in Oracle Database 19c Release Update 19.11, and they are supported in all later releases.
Prerequisites
The COMPATIBLE initialization parameter must be set to the appropriate value before immutable tables can be used.
[oracle@Primary ~]$ sqlplus / as sysdba
NOTE: Verify the database version and apply the appropriate COMPATIBLE value accordingly.
SQL> STARTUP
SQL> ALTER SYSTEM SET compatible=’19.11.0′ SCOPE=SPFILE;
System altered.
SQL> SHUT IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.
Oracle Version Requirements
Immutable Tables were introduced in Oracle Database 19c RU 19.11.
Oracle 19.11 contains a few known issues related to retention policy modifications. These issues are resolved in Oracle 19.12 and later releases, including Oracle 21.3.
For production environments, Oracle recommends using the latest supported Release Update (RU).
Creating an Immutable Table
To create an immutable table, the IMMUTABLE keyword must be included in the CREATE TABLE statement.
In addition to this keyword, Oracle provides two immutable clauses that control data protection and retention behavior:
- NO DROP
- NO DELETE
NO DROP Clause (Table-Level Protection)
The NO DROP clause defines how long the table itself is protected from being dropped.
If the table contains no rows, it can still be dropped.
Unlike early implementations of blockchain tables, this clause also prevents the table from being removed using a DROP USER … CASCADE command.
Syntax
NO DROP [ UNTIL number DAYS IDLE ]
Options
NO DROP
Prevents the table from being dropped indefinitely.
Once data is inserted, the table becomes permanent.
This option should be used with caution, especially in test environments.
NO DROP UNTIL number DAYS IDLE
Prevents the table from being dropped until no new rows have been inserted for the specified number of days.
Each new insert resets the idle timer.
For testing purposes, a value of 0 or 1 day is often recommended.
NO DELETE Clause (Row-Level Protection)
The NO DELETE clause controls the row retention period, defining how long each row is protected from deletion after it is inserted.
Syntax
NO DELETE{
[ LOCKED ]
| (UNTIL number DAYS AFTER INSERT [ LOCKED ])
}
Options
NO DELETE
Retains each row permanently.
Although the absence of the LOCKED keyword may suggest that the retention policy can be modified, retention periods can only be increased and never reduced.
NO DELETE LOCKED
Functions the same as NO DELETE but explicitly locks the retention policy, preventing any future modification.
NO DELETE UNTIL number DAYS AFTER INSERT
Protects each row from deletion for the specified number of days after insertion.
The retention period can be increased later using the ALTER TABLE command.
The minimum allowed retention period is 16 days.
NO DELETE UNTIL number DAYS AFTER INSERT LOCKED
Protects each row for a fixed number of days after insertion and permanently locks the retention policy.
This setting cannot be changed using ALTER TABLE.
The minimum retention period is 16 days.
Example
CREATE IMMUTABLE TABLE imm_audit_log
(
id NUMBER,
action VARCHAR2(100),
log_date DATE)
NO DROP UNTIL 1 DAYS IDLE
NO DELETE UNTIL 30 DAYS AFTER INSERT;
This command creates an immutable table named IMM_AUDIT_LOG that allows only INSERT operations.
The table cannot be dropped until it has been idle (no new inserts) for one day, and each row is protected from deletion for 30 days after it is inserted.
UPDATE operations are not allowed, ensuring data remains tamper-proof during the retention period.
Hidden Columns in Immutable Tables
When an immutable table is created, Oracle automatically adds several hidden system-managed columns.
These columns are similar to those used in blockchain tables.
However, unlike blockchain tables, only a subset of these columns is actively populated.
You can view these columns using the USER_TAB_COLS view:
SELECT internal_column_id,
column_name,
data_type,
data_length,
hidden_column
FROM user_tab_cols
WHERE table_name = ‘IT_T1’ORDER BY internal_column_id;
Among the hidden columns:
- ORABCTAB_CREATION_TIME$and ORABCTAB_USER_NUMBER$ are populated.
- All other blockchain-related columns (hash, signature, chain ID, etc.) remain NULL.
This highlights a key difference between immutable tables and blockchain tables — immutable tables do not use cryptographic chaining.
Viewing Immutable Table Metadata
Oracle provides dedicated dictionary views to display immutable table metadata:
- USER_IMMUTABLE_TABLES
- ALL_IMMUTABLE_TABLES
- DBA_IMMUTABLE_TABLES
These views are based on the internal table:
SYS.IMMUTABLE_TABLE$
Example
SELECT row_retention,
row_retention_locked,
table_inactivity_retention
FROM user_immutable_tables
WHERE table_name = ‘IT_T1’;
This output shows:
- Row retention period (in days)
- Whether the retention policy is locked
- Table inactivity (idle) retention period
Altering an Immutable Table
Modifying the NO DROP Clause
According to documentation, the NO DROP clause can be modified using ALTER TABLE, provided the retention value is not reduced.
However, in Oracle 19c, this behavior is inconsistent.
For tables created with NO DROP UNTIL 0 DAYS IDLE, attempting to increase the value results in an error:
ALTER TABLE it_t1
NO DROP UNTIL 100 DAYS IDLE;
Error
ORA-05732: retention value cannot be lowered
Although the syntax is correct, this appears to be a bug in Oracle 19c.
This issue is resolved in 19.12 and later 21.3 releases.
Additionally, attempting to switch directly to NO DROP (maximum protection) results in an internal error in 19c:
ALTER TABLE it_t1
NO DROP;
Error
ORA-00600: internal error code [atbbctable_1]
This limitation makes testing risky, as starting with NO DROP can only be reversed by dropping the entire schema.
Modifying the NO DELETE Clause
If the retention policy is not locked, the row retention period can be increased but never reduced.
Example
Increase retention:
ALTER TABLE it_t1
NO DELETE UNTIL 32 DAYS AFTER INSERT;
Attempting to reduce it later fails:
ALTER TABLE it_t1
NO DELETE UNTIL 16 DAYS AFTER INSERT;
Error
ORA-05732: retention value cannot be lowered
Similarly, upgrading to NO DELETE (infinite retention) produces an internal error in Oracle 19c but works correctly in Oracle 21.3.
Restricted DML and DDL Operations
As expected for an insert-only structure, immutable tables block any operation that modifies or removes data.
Allowed
INSERT INTO it_t1
VALUES (1, ‘apple’, 20, SYSDATE);
COMMIT;
Not Allowed
- UPDATE
- DELETE
- TRUNCATE
- Adding columns
- Dropping columns
All such operations return:
ORA-05715: operation not allowed on the blockchain or immutable table
Allowed Schema Change
Increasing column length is permitted.
ALTER TABLE it_t1
MODIFY (fruit VARCHAR2(25));
DBMS_IMMUTABLE_TABLE Package
Expired rows cannot be removed using a normal DELETE statement.
Instead, Oracle provides the DBMS_IMMUTABLE_TABLE package.
Example
BEGIN dbms_immutable_table.delete_expired_rows(
schema_name => ‘testuser1’,
table_name => ‘it_t1’,
before_timestamp => NULL,
number_of_rows_deleted => :rows_deleted
);
END;
/
You can also restrict deletion using a timestamp condition.
Rows are deleted only if:
- They are beyond the retention period.
- They match the timestamp criteria.
Best Practices
- Use the latest Oracle 19c RU or later release.
- Choose retention periods carefully since they can only be increased.
- Avoid using NO DROP permanently in test environments.
- Monitor tablespace growth for long retention periods.
- Continue taking regular RMAN backups.
Performance Considerations
Immutable tables provide performance similar to regular heap tables for INSERT operations because they do not maintain cryptographic hash chains like blockchain tables. However, long retention periods can increase storage usage, so proper capacity planning is recommended.
Key Considerations
- Immutable tables prior to Oracle 21.3 exhibit multiple bugs and undocumented behaviors.
- They support indexing and partitioning like regular tables.
- Many restrictions applicable to blockchain tables also apply here.
- Immutable tables are ideal for insert-only, tamper-proof data storage.
- If cryptographic validation is required, blockchain tables may be a better choice.
When Should You Use Immutable Tables?
Use immutable tables when:
- You need regulatory compliance.
- Data must not be altered or deleted during a retention period.
- Audit integrity is required without the overhead of blockchain hashing.
