Monday, May 10, 2021

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

2 comments:

  1. These off-duty flight attendants are willing to do almost everything to amuse our clients.Hotel Sex Service in Lucknow Our off-duty flight attendants are all about class since they earn a fair amount of money from the airline profession.Low budget Call Girls in Lucknow Each of these Housewife Call Girls in Agra has never fails to bring a smile to the faces of Call Girls whatsapp number in Dehradun the clients once the booking is through and Call Girls whatsapp number in Dehradun this is the reason why our Punjabi Escorts in Faridabad clients make repeat bookings for each one of these babes regularly.

    ReplyDelete
  2. In that case, our Agra Escorts can come to a great help providing the best time of your life where you can sense the best process of relaxation and refreshment with gusto.

    Agra Escorts
    Agra call girls

    ReplyDelete