Blockchain and Immutable Tables Enhancements in Oracle Database 23ai

This article highlights several new features introduced in Oracle 23ai for Blockchain and Immutable tables.

Adding and Removing Columns in Blockchain and Immutable Tables

In Oracle 23ai, you can add columns to Blockchain tables (specifically version v2 of these tables):

SQL> CREATE BLOCKCHAIN TABLE blockchaintb1 (
  id    NUMBER(10),
  desc1 VARCHAR2(100)
)
NO DROP UNTIL 20 DAYS IDLE
NO DELETE UNTIL 20 DAYS AFTER INSERT
HASHING USING "SHA2_512" VERSION "v2";  
Table created.

SQL> ALTER TABLE blockchaintb1 ADD (desc2 VARCHAR2(1000));
Table altered.

Similarly, column removal is also supported in this version:

SQL> ALTER TABLE blockchaintb1 DROP COLUMN desc2;
Table altered.

Even after data is inserted into these tables, the above operations can be repeated:

SQL> INSERT INTO blockchaintb1 VALUES(1, 'Amir Hozhabri', 'Sori Masoud');
1 row created.

SQL> COMMIT;
Commit complete.

SQL> ALTER TABLE blockchaintb1 DROP COLUMN desc2;
Table altered.

Note: To check the version of each Blockchain table, you can query the user_blockchain_tables view using the column TABLE_VERSION.

A similar feature is available for version v2 of Immutable tables:

SQL> CREATE IMMUTABLE TABLE Immtb1 (
  id    NUMBER(10),
  desc1 VARCHAR2(100)
)
NO DROP UNTIL 200 DAYS IDLE
NO DELETE UNTIL 200 DAYS AFTER INSERT
VERSION "v2";
Table created.

SQL> ALTER TABLE Immtb1 ADD (desc2 VARCHAR2(1000));
Table altered.

SQL> ALTER TABLE Immtb1 DROP COLUMN desc2;
Table altered.

Configuring Maximum Idle Retention Time

Incorrect configuration of Idle Retention Time can cause challenges. Oracle 23ai introduces a new parameter, BLOCKCHAIN_TABLE_RETENTION_THRESHOLD, which enforces a ceiling for NO DROP UNTIL .. DAYS IDLE.

SQL> ALTER SYSTEM SET BLOCKCHAIN_TABLE_RETENTION_THRESHOLD=45;
System altered.

Without the TABLE RETENTION privilege, users cannot create tables exceeding this threshold:

SQL> CREATE BLOCKCHAIN TABLE blkchaintb1 (
  id    NUMBER(10),
  desc1 VARCHAR2(100)
)
NO DROP UNTIL 2000 DAYS IDLE
NO DELETE UNTIL 30 DAYS AFTER INSERT
HASHING USING "SHA2_512" VERSION "v1";
ORA-05807: Blockchain or immutable table "USEF"."BLKCHAINTB1" cannot have idle retention greater than 45 days.

SQL> CREATE BLOCKCHAIN TABLE blkchaintb1 (
  id    NUMBER(10),
  desc1 VARCHAR2(100)
)
NO DROP UNTIL 44 DAYS IDLE
NO DELETE UNTIL 30 DAYS AFTER INSERT
HASHING USING "SHA2_512" VERSION "v1";
Table created.

Granting the TABLE RETENTION privilege overrides this restriction:

SQL> GRANT TABLE RETENTION TO usef;
Grant succeeded.

SQL> CONNECT usef/a@OEL8:1521/TEST
Connected.

SQL> CREATE BLOCKCHAIN TABLE blkchaintb1 (
  id    NUMBER(10),
  desc1 VARCHAR2(100)
)
NO DROP UNTIL 2000 DAYS IDLE
NO DELETE UNTIL 30 DAYS AFTER INSERT
HASHING USING "SHA2_512" VERSION "v1";
Table created.

Blockchain Table Row Versions

As you know, records in Blockchain tables cannot be updated:

SQL> CREATE BLOCKCHAIN TABLE blkchaintb1 (
  id    NUMBER(10),
  desc1 VARCHAR2(100)
)
NO DROP UNTIL 2000 DAYS IDLE
NO DELETE UNTIL 30 DAYS AFTER INSERT
HASHING USING "SHA2_512" VERSION "v1";
Table created.

SQL> INSERT INTO blkchaintb1 VALUES(1, 'usefzadeh.co');
1 row created.

SQL> UPDATE blkchaintb1 SET desc1='usefzadeh.com' WHERE id=1;
ORA-05715: operation not allowed on the blockchain or immutable table

For corrections or updates, new records must be inserted. Identifying the latest version of a record becomes a challenge.

Oracle 23ai addresses this with the Row Versions feature. By including WITH ROW VERSION AND USER CHAIN in the CREATE TABLE statement, a view (_LAST$) is generated to display the latest version of each record.

SQL> CREATE BLOCKCHAIN TABLE blkchaintb3 (
  id    NUMBER(10),
  desc1 VARCHAR2(100)
)
NO DROP UNTIL 2000 DAYS IDLE
NO DELETE UNTIL 30 DAYS AFTER INSERT
HASHING USING "SHA2_512"
WITH ROW VERSION AND USER CHAIN fruit_chain (id)
VERSION "v2";  
Table created.

SQL> INSERT INTO blkchaintb3 VALUES(1, 'usefzadeh.co');
1 row created.

SQL> INSERT INTO blkchaintb3 VALUES(1, 'usefzadeh.com');
1 row created.

SQL> INSERT INTO blkchaintb3 VALUES(2, 'test');
1 row created.

SQL> COMMIT;
Commit complete.

The view blkchaintb3_last$ displays the latest version of each id:

SQL> DESC blkchaintb3_last$;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(10)
 DESC1                                              VARCHAR2(100)

SQL> SELECT * FROM blkchaintb3_last$;
        ID DESC1
---------- ---------------
         1 usefzadeh.com
         2 test