Immutable tables is a new Oracle database feature introduced in 19c lately and they are “read-only” tables that prevent unauthorized data modifications. The objective of having immutable tables is to have “insert only” table to protect against insider threat (someone with access attempting to modify data) and external hackers. Moreover, immutable table must have retention period both for the immutable table and for rows within the immutable table. Rows become obsolete after the specified row retention period. Only obsolete rows can be deleted from the immutable table.

If you are wondering by now what is the difference between Blockchain tables and Immutable Tables, you can first read my blog post about blockchain tables here : https://geodatamaster.com/2021/05/08/oracle-database-19c-blockchain-tables/

Also, Oracle has a good summarized table comparison: https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-tables.html#GUID-F537DD20-58ED-450A-837C-372E3BDA12F9

Example of creating immutable table (that can’t be dropped for 15 days, and inserted row can’t be deleted until 20 days are past from the time of insertion):

SQL> CREATE IMMUTABLE TABLE HR.trade_ledger (id NUMBER, luser VARCHAR2(40), value NUMBER)

       NO DROP UNTIL 15 DAYS IDLE    

       NO DELETE UNTIL 20 DAYS AFTER INSERT;

SQL> insert into HR.trade_ledger values (‘100′,’Emad’,’2210′);

SQL> commit;

If you attempt to delete records from the table, as expected an error will be thrown:

SQL> delete from HR.trade_ledger ;

ORA-05715: operation not allowed on the blockchain or immutable table

You can change/modify retention period of a table to a “higher” value:

SQL> ALTER TABLE HR.trade_ledger NO DROP UNTIL 20 DAYS IDLE;