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.
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 188.8.131.52.0 or greater ORA-00722: Feature "Immutable table" compatible string 19.0.0
# 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;
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.
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 184.108.40.206.0 - Production on Tue Nov 9 12:43:31 2021 Version 220.127.116.11.0 Copyright (c) 1982, 2021, Oracle. All rights reserved. Connected to: Oracle Database 21c Express Edition Release 18.104.22.168.0 - Production Version 22.214.171.124.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
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.
DROP TABLE examples.trade_ledger;