Immutable Tables in Oracle Database 19c and 21c
What are Immutable Tables?
Immutable tables in Oracle Database 19c and 21c offer a solution for maintaining unalterable records. Unlike traditional tables, data within immutable tables is write-once, meaning rows cannot be updated or deleted once inserted. This is particularly valuable in regulatory environments that require secure, unmodifiable records.
Prerequisites for Using Immutable Tables
To activate immutable tables, ensure the COMPATIBLE parameter in the database is set to a version that supports them. Here are the compatibility configurations for 19c and 21c:
-- Connect as sysdba
conn / as sysdba
-- Oracle 19c
ALTER SYSTEM SET COMPATIBLE = '19.11.0' SCOPE = SPFILE;
-- Oracle 21c
ALTER SYSTEM SET COMPATIBLE = '21.0.0' SCOPE = SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;
**Note**: Adjusting the COMPATIBLE parameter can have a significant impact on the database. Always evaluate carefully and avoid making changes unless necessary.
Key Features of Immutable Tables
Immutable tables provide unique features that enforce data integrity and permanence:
- Insert-only: New data can be added, but existing rows cannot be modified or deleted.
- Retention Policies: The retention period can be specified, protecting rows from deletion for a defined duration.
- Security and Compliance: Immutable tables aid in meeting strict data compliance and regulatory requirements.
Creating an Immutable Table
To create an immutable table, specify the IMMUTABLE keyword within the CREATE TABLE command. Include the NO DROP and NO DELETE clauses for retention control:
CREATE IMMUTABLE TABLE demo_immutable (
id NUMBER PRIMARY KEY,
description VARCHAR2(255),
created_date DATE DEFAULT SYSDATE
) NO DROP UNTIL 5 DAYS IDLE NO DELETE UNTIL 30 DAYS AFTER INSERT;
The NO DROP clause restricts the table from being dropped until it remains idle (no new rows added) for the specified number of days. The NO DELETE clause protects rows from deletion for a defined period.
Modifying Immutable Tables
Modifications to an immutable table are limited. You can alter certain retention settings, but reductions in retention are restricted to maintain data integrity:
-- Increase retention for deletion prevention
ALTER TABLE demo_immutable NO DELETE UNTIL 60 DAYS AFTER INSERT;
Reducing a retention setting is not permitted, as it could compromise the security model of immutable tables.
Limitations and Considerations
- No Update/Delete: Rows in immutable tables cannot be modified or deleted, ensuring data remains unaltered.
- Compatibility: Immutable tables require a compatible database setting, which may limit backward compatibility.
- Restrictions on DDL: Some DDL operations, like adding or dropping columns, are restricted.
Examples and Best Practices
Here are some additional examples and recommendations for using immutable tables:
-- Example: Configuring an immutable table for financial data
CREATE IMMUTABLE TABLE financial_records (
transaction_id NUMBER PRIMARY KEY,
amount NUMBER,
transaction_date DATE
) NO DROP UNTIL 0 DAYS IDLE NO DELETE UNTIL 365 DAYS AFTER INSERT;
-- Alter retention setting
ALTER TABLE financial_records NO DELETE UNTIL 500 DAYS AFTER INSERT;
Conclusion
Immutable tables in Oracle Database 19c and 21c are valuable for secure, tamper-resistant data storage. Properly configured, they support compliance and data integrity, especially in sectors requiring stringent data protection. By enforcing retention periods and preventing modifications, immutable tables enhance data security and regulatory compliance.