Audit trails, compliance logs, and financial records must not be changed after they are written, not even by a privileged user. An immutable table enforces that in the database: rows can be inserted, but never updated, and deleted only after a retention period; the table itself cannot be dropped until it has been idle for a set time.
Code for This Guide
The main examples are in the examples/special-tables folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
create immutable table table (...) no drop [until n days idle] no delete [locked | until n days after insert]
| Clause | Effect |
|---|---|
| NO DROP UNTIL n DAYS IDLE | The table can be dropped only after no rows were inserted for n days; NO DROP alone never |
| NO DELETE UNTIL n DAYS AFTER INSERT | A row can be deleted only n days after it was inserted (at least 16) |
| NO DELETE LOCKED | Rows can never be deleted |
Insert Only
Example:
create immutable table fare_audit ( audited_at timestamp default localtimestamp, route_id number, old_fare number, new_fare number ) no drop until 0 days idle no delete until 16 days after insert; insert into fare_audit (route_id, old_fare, new_fare) values (1, 500, 520); commit; update fare_audit set new_fare = 999; delete from fare_audit;
Output:
Immutable TABLE created. 1 row inserted. Commit complete. Error starting at line : 12 In command - update fare_audit set new_fare = 999 Error at Command Line : 12 Column : 8 Error report - SQL Error: ORA-05715: operation not allowed on the blockchain or immutable table Error starting at line : 13 In command - delete from fare_audit Error at Command Line : 13 Column : 13 Error report - SQL Error: ORA-05715: operation not allowed on the blockchain or immutable table
The insert works; the UPDATE and DELETE fail with ORA-05715. The example uses NO DROP UNTIL 0 DAYS IDLE so that the table can be dropped afterward with drop table fare_audit purge; real audit tables use longer periods.
Check the Retention
Example:
select table_name, row_retention, table_inactivity_retention from user_immutable_tables where table_name = 'FARE_AUDIT2';
Output:
TABLE_NAME ROW_RETENTION TABLE_INACTIVITY_RETENTION ______________ ________________ _____________________________ FARE_AUDIT2 16 0
USER_IMMUTABLE_TABLES shows the row retention in days and the inactivity period before the table can be dropped.
Things to Know
- Retention periods can be increased later with ALTER TABLE, but not decreased.
- Expired rows are removed with DBMS_IMMUTABLE_TABLE.DELETE_EXPIRED_ROWS.
- For tamper evidence on top of tamper resistance, use a blockchain table, which also chains rows with cryptographic hashes.
Related Guides
Conclusion
Immutable tables accept inserts only: updates are refused, deletes wait for the retention period, and the table cannot be dropped until it has been idle. Use them for audit and compliance data that must not change.
