Managing Immutable Tableas in ORacle 19c and Oracle 21c

Oracle 19c (19.11) introduced Immutable tables, these tables provides protection against unauthorized data modification that means these are read-only tables.

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;

The following are NOT supported with immutable tables:
· Creating immutable tables in the CDB root or application root
· Online redefinition using the DBMS_REDEFINITION package
· Truncating the immutable table
· Flashback table
· Sharded tables
· Adding columns, renaming columns
· Dropping columns, and dropping partitions
· Updating rows, merging rows
· Logical Standby and Oracle GoldenGate
· Direct-path loading and inserting data using parallel DML
· Defining BEFORE ROW triggers that fire for update operations
· Creating Automatic Data Optimization (ADO) policies
· Creating Oracle Label Security (OLS) policies
· Transient Logical Standby and rolling upgrades
· Converting a regular table to an immutable table or vice versa

Creating Immutable tables

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.

DML on Immutable Tables: Except insert, DML are not allowed

• INSERT

SQL> INSERT INTO IMMU_TAB1 values ( 1,’TEST’,sysdate);
1 row inserted.
SQL> commit;
Commit complete.

• DELETE

SQL> DELETE FROM IMMU_TABL1 WHERE COLA = 1;
Error report -
SQL Error: ORA-05715: operation not allowed on the blockchain or immutable table

• UPDATE

SQL> update IMMU_TAB1 set COLB = ‘TEST2’ where COLA= 1;
Error report -
SQL Error: ORA-05715: operation not allowed on the blockchain or immutable table

DDL on Immutable Tables

• TRUNCATE TABLE – This operation is now allowed as it deletes the rows

sql> TRUNCATE TABLE IMMU_TAB1;
Error report -
ORA-05715: operation not allowed on the blockchain or immutable table

• ALTER RETENTION- User will get below error when try to reduce the retention period.

SQL> ALTER TABLE IMMU_TAB1 NO DROP UNTIL 2 DAYS IDLE;
Error report -
ORA-05732: retention value cannot be lowered

SQL> ALTER TABLE IMMU_TAB1 NO DELETE UNTIL 20 DAYS AFTER INSERT;
Error report -
ORA-05732: retention value cannot be lowered

SQL> ALTER TABLE IMMU_TAB1 NO DELETE;
Error report -
ORA-00600: internal error code, arguments: [atbbctable_1], [0], [], [], [], [], [], [], [], [], [], []

When trying to modify the rows to NO DELETE, we got ORA-600 error and it may be related to bug and contact Oracle for support.

• MODIFY COLUMN - User can run below command to extend column length

SQL> ALTER TABLE IMMU_TAB1 MODIFY (COLB VARCHAR2(30));
Table IMMU_TAB1 altered.

• DROP COLUMN - User will get below error when dropping column as this operation is not allowed

SQL> ALTER TABLE IMMU_TAB1 DROP COLUMN COLB;
Error report -
ORA-05715: operation not allowed on the blockchain or immutable table

• ADD COLUMN - User will get below error when adding a new as this operation is not allowed

SQL> ALTER TABLE IMMU_TAB1 ADD (COLD VARCHAR2(10));
Error report -
ORA-05715: operation not allowed on the blockchain or immutable table

User can use DBMS_IMMUTABLE_TABLE.DELETE_EXPIRED_ROWS procedure to delete all rows that are beyond the specified retention period or obsolete

Example1: The below example deletes the rows that were created 31 days before the current system date.

DECLARE
NROWS NUMBER;
BEGIN
DBMS_IMMUTABLE_TABLE.DELETE_EXPIRED_ROWS('TESTUSR','IMMU_TAB1', SYSDATE-31, NROWS);
DBMS_OUTPUT.PUT_LINE('NO_OF_ROWS_DELETED=' || NROWS);
END;
/

Example2: The below procedure delete the rows that are beyond the retention period or obsolete

 

DECLARE

       NROWS NUMBER;

BEGIN

       DBMS_IMMUTABLE_TABLE.DELETE_EXPIRED_ROWS(

       SCHEMA_NAME   =>'TESTUSR',

       TABLE_NAME    => 'IMMU_TAB1',

       BEFORE_TIMESTAMP  => NULL,);

       DBMS_OUTPUT.PUT_LINE('NUMBER_OF_ROWS_DELETED=' || NROWS);

END;

/

• DROP IMMUTABLE Table - The immutable table must be in the owner schema or must have the DROP ANY TABLE system privilege.

SQL> DROP TABLE IMMU_TAB1;

Oracle Database does not prevent flashback and point-in-time recovery operations to undo changes on immutable tables as these may be required to undo logical and physical corruptions. These recovery operations on a database undo the changes made to all tables, including immutable tables.

Note that retention policies in immutable tables relies on the system time.

Thanks & Regards,

http://oracleracexpert.com, Oracle ACE