The block linked list of Oracle 21c is the most concerned feature, but there is another small feature rarely mentioned, that is Immutable tables - Immutable tables.

The block linked list was introduced in 21c and then transplanted back to 19.10. The immutable list was introduced into Oracle 21.3 and 19.11 at the same time. Therefore, it can be considered as a new feature of 19c and 21c.

Parameter setting

To use this feature, you need to set the compatible parameter of the database above 19.11.0 (please evaluate the impact of this parameter in advance).

Otherwise, you will receive the following error reports:

ERROR at line 1: 
ORA-00406: COMPATIBLE parameter needs to be 19.11.0.0.0 or greater 
ORA-00722: Feature "Immutable table" compatible string 19.0.0

For example:

# Oracle Database 19c
alter system set compatible='19.11.0' scope=spfile;

# Oracle Database 21c
alter system set compatible='21.0.0' scope=spfile;

shutdown immediate;
startup;

Main characteristics

Immutable table inherits the similar syntax of Blockchain Table, but removes the link of internal row records to improve performance.
The main benefits of immutable tables are as follows:

Immutable table is a read-only table, which can prevent unauthorized data modification by internal personnel and accidental data modification caused by human errors.

It can prevent unauthorized modification by infected or malicious employees.

You can add new rows to an immutable table, but you cannot modify existing rows.

Retention periods must be specified for immutable tables and rows in immutable tables. The row becomes obsolete after the specified row retention period. Only obsolete rows can be deleted from immutable tables.

Immutable tables contain system generated hidden columns. The columns are the same as those of the block linked list. When a row is inserted, it is ORABCTAB_CREATION_TIME $and orabctab_ USER_ Set a non NULL value for the number $column. The values of the remaining system generated hidden columns are set to NULL.

Using immutable tables does not require changes to existing applications.

example

The following is an example of creating an Immutable Table:

CREATE IMMUTABLE TABLE mogdb (id NUMBER, duser VARCHAR2(40), value NUMBER)
NO DROP UNTIL 40 DAYS IDLE
NO DELETE UNTIL 100 DAYS AFTER INSERT;

The following tests were conducted in the training environment of Oracle 21c Express version of motianlun:

bash-4.4$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Tue Nov 9 12:43:31 2021
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> show pdbs;

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

SQL> alter session set container=XEPDB1;

Session altered.

SQL> CREATE IMMUTABLE TABLE mogdb (id NUMBER, duser VARCHAR2(40), value NUMBER)
       NO DROP UNTIL 40 DAYS IDLE    
       NO DELETE UNTIL 100 DAYS AFTER INSERT;    

Table created.

SQL> insert into mogdb values(1,'zCloud',100);

1 row created.

SQL> insert into mogdb values(2,'openGauss',200);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from mogdb;

    ID DUSER                     VALUE
---------- ---------------------------------------- ----------
     1 zCloud                       100
     2 openGauss                       200

SQL> update mogdb set value=300;
update mogdb set value=300
       *
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain or immutable table

Data maintenance

Rows can be deleted from an immutable table only if the data exceeds the retention period.

The following example deletes the immutable table trade when connecting as SYS_ All rows outside the window are retained in the ledger. The number of rows deleted is stored in the output parameter num_rows.

DECLARE
num_rows NUMBER;
BEGIN
DBMS_IMMUTABLE_TABLE.DELETE_EXPIRED_ROWS('EXAMPLES','TRADE_LEDGER', NULL, num_rows);
DBMS_OUTPUT.PUT_LINE('Number_of_rows_deleted = ' || num_rows);
END;
/

The following example deletes obsolete rows created 30 days before the current system date when connecting as SYS. The number of rows deleted is stored in the output parameter num_rows.

DECLARE
num_rows NUMBER;
BEGIN
DBMS_IMMUTABLE_TABLE.DELETE_EXPIRED_ROWS('EXAMPLES','TRADE_LEDGER', SYSDATE-30, num_rows);
DBMS_OUTPUT.PUT_LINE('Number_of_rows_deleted=' || num_rows);
END;
/

When you try to modify the data of the immutable table, you will receive an error message similar to the following:

SQL Error: ORA-05715: operation not allowed on the blockchain or immutable table

If the immutable table is empty or has not been modified within a period of time defined by its retention period, you can delete the immutable table.

For example:

DROP TABLE examples.trade_ledger;