Immutable table in oracle database 19c
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).…
Read More