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
SQL> INSERT INTO IMMU_TAB1 values ( 1,’TEST’,sysdate);
1 row inserted.
SQL> commit;
Commit complete.
SQL> DELETE FROM IMMU_TABL1 WHERE COLA = 1;
Error report -
SQL Error: ORA-05715: operation not allowed on the blockchain or immutable table
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
