[26ai] Blockchain & Immutable table

If you prefer to read in Spanish Spanish version.

Today, we’re going to talk about Blockchain tables and Immutable tables.

Both were introduced in Oracle in order to protect against unauthorized data modification by external user, you can only insert rows. Unlike Immutable tables, Blockchain tables include a cryptographic hash for every row.

Even though it’s not a feature in 26ai, it was available in 21c. However, several new features have been introduced in the latest release such as:

• Adding and removing columns in Blockchain and Immutable tables, it’s always specifically VERSION v2 of these tables. Below you can see how it works:

    SQL> 
    CREATE IMMUTABLE TABLE T_TEST_VRS_1 (
      TC_ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
    )
    no drop until 2 days idle
    no delete until 16 days after insert
    VERSION "v1";
  
    Table created.
    SQL>  ALTER TABLE T_TEST_VRS_1 ADD (FECHA DATE);
    ERROR at line 1:
    ORA-05715: operation not allowed on the blockchain or immutable table
    Help: https://docs.oracle.com/error-help/db/ora-05715/
  
    SQL> 
    CREATE IMMUTABLE TABLE T_TEST_VRS_2 (
      TC_ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
    )
    no drop until 2 days idle
    no delete until 16 days after insert
    VERSION "v2";
  
    SQL> ALTER TABLE T_TEST_VRS_2 ADD (FECHA DATE);
    Table altered.
  

• BLOCKCHAIN_TABLE_MAX_NO_DROP, a new parameter that controls the amount of idle time the table must be inactive before it can be dropped. When the parameter has a value and a user creates a new table with NO DROP UNTIL n DAYS IDLE clause, the value of n must be less than or equal to the parameter. However, if the parameter is null, any value can be used in the clause.

Rows can’t be deleted while it is within the specified retention period. The same applies to dropping the table. When the retention period expires, you can delete rows or drop the table.

Retention Period

In the following diagram, we can see the different options available in order to set up our tables:

Table level:

• NO DROP: The table can’t be dropped unless it’s empty.

• NO DROP UNTIL n DAYS IDLE: If there are rows within the specified retention period, the table can’t be dropped. By default, it’s 0, although a minimal value of 16 is recommended.

Row level:

• NO DELETE [locked]: Each row is retained forever. La clausula [locked] especifica que no se puede modificar la retención.

• NO DELETE UNTIL n DAYS AFTER INSERT [locked]: Cada fila no puede ser eliminada, es decir, esta protegida de la eliminación tantos días como especifiquemos en la cláusula n DAYS. Valor mínimo es 16 días. La clausula [locked] especifica que no se puede modificar la retención.

Restrictions

Be aware of the following points:

• The following data types are not supported: ROWID, UROWID, LONG, OBJECT TYPE, REF, VARRAY, NESTED TABLE, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, BFILE y XMLType.

• It can’t be INDEX-ORGANIZED, ORGANIZATION CUBE, ORGANIZATION EXTERNAL o HIBRYD PARTITIONED.

So, let's get down to business!

Inmutable Table

SQL> 
CREATE IMMUTABLE TABLE T_CLIENTES (
  TC_ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  TC_name   VARCHAR2(100) NOT NULL,
  TC_email       VARCHAR2(200),
  TC_created_at  DATE DEFAULT SYSDATE
)
no drop until 2 days idle
no delete until 16 days after insert
VERSION "v2";

Table created.

If we need to modify the retention period, we can do it as follows:

SQL> ALTER TABLE T_CLIENTES  NO DROP UNTIL 5 DAYS IDLE;
Table altered.
SQL> ALTER TABLE T_CLIENTES  NO DELETE UNTIL 32 DAYS AFTER INSERT;
Table altered.

Let’s add columns,as the table uses the VERSION v2 clause:

SQL> ALTER TABLE T_CLIENTES ADD (TC_PROVINCIA VARCHAR2(100));

Let’s insert some data and try to modify it:

SQL> r
  1* Select * From t_Clientes

     TC_ID TC_NAME              TC_EMAIL             TC_CREATE TC_PROVINCIA
---------- -------------------- -------------------- --------- ----------------------------------------------------------------------------------------------------
         1 Iker San             San@demo.local       11-FEB-26
         2 Juan Perro           Perro@demo.local     11-FEB-26
         3 Carla Marq           Marq@demo.local      11-FEB-26

SQL> Update t_Clientes Set Tc_Provincia='VALLADOLID' where tc_id = 3;
Update t_Clientes Set Tc_Provincia='VALLADOLID' where tc_id = 3
       *
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain or immutable table
Help: https://docs.oracle.com/error-help/db/ora-05715/

As we can see, we can’t modify the information, we can only insert new registers.

Blockchain Table

SQL> 
CREATE BLOCKCHAIN TABLE T_PRODUCTOSTIENDA (
  TP_id       NUMBER,
  TP_NOMBRE   VARCHAR2(100),
  TP_PRICE    FLOAT
)
NO DROP UNTIL 14 DAYS IDLE
NO DELETE UNTIL 30 DAYS AFTER INSERT
HASHING USING "SHA2_512"
WITH ROW VERSION AND USER CHAIN T_PRODUCTOS_VALIDATION (TP_id)
VERSION "v2";  

Table created.

If we need to modify the retention period, we can do it as follows:

SQL> ALTER TABLE T_PRODUCTOSTIENDA  NO DROP UNTIL 16 DAYS IDLE;
Table altered.
SQL> ALTER TABLE T_PRODUCTOSTIENDA  NO DELETE UNTIL 32 DAYS AFTER INSERT;
Table altered.

Let’s add columns,as the table uses the VERSION v2 clause:

SQL> ALTER TABLE T_PRODUCTOSTIENDA ADD (TC_CANTIDAD INTEGER);

Let’s insert some data and try to modify it:

SQL> Select * From T_PRODUCTOSTIENDA;

     TP_ID TP_NOMBRE              TP_PRICE TC_CANTIDAD
---------- -------------------- ---------- -----------
         1 PERAS
         1 PERAS JULIANAS
         2 NARANJAS
         3 MANGOS

SQL> UPDATE T_PRODUCTOSTIENDA SET TP_PRICE = 9;
UPDATE T_PRODUCTOSTIENDA SET TP_PRICE = 9
       *
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain or immutable table
Help: https://docs.oracle.com/error-help/db/ora-05715/

As we can see, we can’t modify the information, we can only insert new registers. If you look at the SQL output, we can see two rows with the same tp_id, but…which one was inserted last? In this type of table, Oracle automatically generates a view called <TABLE_NAME>_LAST$ in order to display the latest version of each record.

SQL> Select * From T_PRODUCTOSTIENDA_LAST$;

     TP_ID TP_NOMBRE              TP_PRICE
---------- -------------------- ----------
         1 PERAS JULIANAS
         2 NARANJAS
         3 MANGOS

In future articles, we will cover the topic of deletion based on the examples we’ve used.

Looking forward to seeing you in the next article :)