Immutable tables in Oracle 19c or 21c

Oracle 19c (19.11) introduced Immutable tables, these tables provides protection against unauthorized data modification that means these are read-only tables. User can able add or insert the rows to the table but cannot able to modify. In order to manage the data the user must specify retention period for both immutable table and for the rows with in the table.

The immutable tables will have hidden columns and these are same as Block chain tables introduced in Oracle 21c. Based upon the retention period rows become obsolete in the tableau then only user can be able to delete the rows.

In order to use this feature user must set set the COMPATIBLE parameter set to 19.11.0
SQL> alter system set compatible='19.11.0' scope=spfile;

There are several data types are not supported with immutable tables ROWID, UROWID, LONG, object type, REF, varray, nested table, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, BFILE, and XMLType and XMLType tables are not supported.

Immutable tables contain system-generated hidden columns and they are the same as blockchain tables. When a row is inserted, a non-NULL value is set for the ORABCTAB_CREATION_TIME$ and ORABCTAB_USER_NUMBER$ columns,  value of remaining system-generated hidden columns is set to NULL.

When creating IMMUTABLE TABLE statement user should use below NO DROP, NO DELETE to specify retention period. The LOCKED keyword specifies table or rows retention setting cannot be modified.

1. NO DROP clause in CREATE IMMUTABLE TABLE statement for retention period

• NO DROP : If you use this clause without any retention then then you cannot able to drop and user needs to be careful when using this clause
• NO DROP UNTIL number DAYS IDLE : By using retention user can able to drop the but User cannot able to drop the table if newest row is less than n days old

To set the table retention period to 0 days, the initialization parameter BLOCKCHAIN_TABLE_MAX_NO_DROP must be set to 0. Pls note that the minimum value is 16

2. NO DELETE clause to specify the retention period for the rows

• NO DELETE [LOCKED] – If you use without any retention then Rows cannot be deleted from the immutable table
• 
  
• NO DELETE UNTIL n DAYS AFTER INSERT [LOCKED] – if you use this clause the row cannot be deleted until n days after it was added.

User can use ALTER TABLE statement with the NO DELETE UNTIL clause to modify this setting and increase the retention period but cannot reduce the retention period. The minimum value for n is 16 days. If you use LOCKED then you cannot subsequently modify the row retention.

SQL> CREATE IMMUTABLE TABLE IMMU_TAB1 (
COLA NUMBER,
COLB VARCHAR2(15),
COLC DATE
) NO DROP UNTIL 3 DAYS IDLE
NO DELETE UNTIL 30 DAYS AFTER INSERT;

The user can able to drop table only after 3 days of inactivity and cannot deleted rows until 30 days after it has been inserted.

Use the DBMS_IMMUTABLE_TABLE.DELETE_EXPIRED_ROWS procedure to delete rows beyond the specified retention period or obsolete . Either SYS user or the owner of the schema can delete immutable table rows.

In order to DROP immutable table, it must be the owner of the schema or must have the DROP ANY TABLE system privilege.

SELECT row_retention "Row Retention Period", row_retention_locked "Row Retention Lock", table_inactivity_retention "Table Retention Period" FROM dba_immutable_tables
WHERE table_name = 'IMMU_TAB1';

Row Retention Period Row Retention Locked Table Retention Period
-------------------- -------------------- ----------------------
30 NO 3

User can query {CDB|DBA|ALL|USER}_IMMUTABLE_TABLES views information about immutable tables.

Regards,

http://oracleracexpert.com, Oracle ACE