Mir Sayeed Hassan – Oracle Blog

Oracle 21C New Features: Immutable Tables

Overview of Immutable table:

– The Functionality of the immutable table is inherits the similar syntax of Native Blockchain Table, but removes the link of internal row records to improve performance.
– Benifits of the this immutable table is a read-only table, this can prevent the unauthorized/accidental/hacks/illegal changes in data modification/manipulation by human.
– Immutable table you can add new rows into it but you cannot modify/delete existing rows & even DBA role cannot modify there tables.
– Retention periods must be specified for immutable tables and rows in immutable tables, therfore this row becomes an obsolete after the specified row retention period.
– As only obsolete rows can be deleted from immutable tables.

Check the status of database

SQL> select instance_name, version, open_mode from v$instance, v$database;

INSTANCE_NAME      VERSION        OPEN_MODE
---------------- ---------------------------
ora21c           21.0.0.0.0      READ WRITE

You cannot create the immutable table in container database, You can create only on PDBs

Container Database:

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> create immutable table test_immutable (
eno number(10), 
name varchar2(20), 
address varchar2(20), 
created_date date, 
constraint test_immutable_pk primary key (eno)
)
no drop until 10 days idle
no delete until 30 days after insert;

create immutable table test_immutable (
*
ERROR at line 1:
ORA-05729: blockchain or immutable table cannot be created in root container

Connect to the Pluggable Database(PDB)

SQL> show pdbs

CON_ID   CON_NAME    OPEN MODE   RESTRICTED
--------------------------------------------
2        PDB$SEED    READ ONLY     NO
3        PDB1        READ WRITE    NO

SQL> alter session set container=pdb1;
Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDB1

SQL> show user
USER is "SYS"

SQL> create immutable table test_immutable (
eno number(10),
name varchar2(20), 
address varchar2(20), 
created_date date, 
constraint test_immutable_pk primary key (eno)
)
no drop until 10 days idle
no delete until 30 days after insert;

Table created.

Verify by using the data dictionary

SQL> desc dba_immutable_tables;

Name            Null?                 Type
--------------------------------------------
SCHEMA_NAME     NOT NULL          VARCHAR2(128)
TABLE_NAME      NOT NULL          VARCHAR2(128)
ROW_RETENTION   NUMBER
ROW_RETENTION_LOCKED              VARCHAR2(3)
TABLE_INACTIVITY_RETENTION        NUMBER

Check the retention period set

SQL> 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 = 'TEST_IMMUTABLE';

Row Retention    Period Row Table     Retention Period
-------------------------------------------------------
       30               NO                     10

Now let’s try to insert records into the table “TEST_IMMUTABLE”

SQL> desc test_immutable;

Name                Null?             Type
----------------------------------------- -------- ----------------------------
ENO                NOT NULL          NUMBER(10)
NAME                                 VARCHAR2(20)
ADDRESS                              VARCHAR2(20)
CREATED_DATE                         DATE

SQL> select * from test_immutable;
no rows selected

SQL> insert into test_immutable values(1,'mir','india',sysdate);
1 row created.

SQL> insert into test_immutable values(2,'hassan','turkey',sysdate);
1 row created.

SQL> select * from test_immutable;

ENO         NAME         ADDRESS        CREATED_D
-----------------------------------------------------------
1           mir           india         20-SEP-22
2          hassan         turkey        20-SEP-22

Let’s update/delete the above immutable table

SQL> update test_immutable set eno=3 where name='HASSAN';
update test_immutable set eno=3 where name='HASSAN'
*
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain or immutable table

SQL> delete from test_immutable where eno=3;
delete from test_immutable where eno=3
*
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain or immutable table

Let’s alter the NO DELETE clause.

SQL> alter table test_immutable no delete until 50 days after insert;
Table altered.

Check the retention period again

SQL> 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 = 'TEST_IMMUTABLE';

Row Retention    Period Row Table     Retention Period
-------------------------------------------------------
     60            NO                       10

In-case if you want to lower down the retention period

SQL> alter table test_immutable no delete until 50 days after insert;
alter table test_immutable no delete until 50 days after insert
*
ERROR at line 1:
ORA-05732: retention value cannot be lowered

Let’s alter the NO DROP clause.

SQL> alter table test_immutable no drop until 20 days idle;
Table altered.

In-case if you want to lower down the retention period

SQL> alter table test_immutable no drop until 10 days idle;
alter table test_immutable no drop until 10 days idle
*
ERROR at line 1:
ORA-05732: retention value cannot be lowered

SQL> alter table test_immutable no drop;
Table altered.

SQL> 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 = 'TEST_IMMUTABLE';

Row Retention      Period Row Table       Retention Period
----------------------------------------------------------
     60                  NO                    365000

Even you cannot add/drop/truncate a column in immuatble table

SQL> alter table test_immutable add (salary number(20));
alter table test_immutable add (salary number(20))
*
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain or immutable table

SQL> alter table test_immutable drop column name;
alter table test_immutable drop column name
*
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain or immutable table

SQL> truncate table test_immutable;
truncate table test_immutable
*
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain or immutable table

So far there is no effect on table, verify

SQL> select * from TEST_IMMUTABLE;

ENO       NAME      ADDRESS          CREATED_D
-----------------------------------------------
1         mir       india          20-SEP-22
2        hassan    turkey          20-SEP-22

SQL> drop table test_immutable;
drop table test_immutable
*
ERROR at line 1:
ORA-05723: drop blockchain or immutable table TEST_IMMUTABLE not allowed

Note: The test_immutable table cannot be drop due to the retuention policy set for table, therefore you can drop table only after retention policy completed.

=====Hence done the various scenario as per expected on immutable table in oracle 21c database=====