Techblogs

Immutable tables

Terug naar overzicht

19 februari 2024

Oracle Database 21c introduced a new concept for tables. We have had heap tables, index organized tables (the original IOT in my opinion), temporary tables, and now we also get immutable tables and blockchain tables. Immutable tables are like normal tables, but with a lot of restrictions.

Once the table has been created, you cannot modify its structure. You can add and remove indexes, however. You can only insert rows, updating rows is not permitted, you can only delete rows if the insert occurred a set number of days ago.

Creating an immutable table is very much like creating a regular table, only with a couple extra settings you have to do.

First, you have to tell Oracle Database you are creating an immutable table, by using the immutable keyword. After specifying the columns, constraints, etc. you have to add two clauses specifying the rules for this table. One is the period in which the table must be idle, i.e. no inserts or deletes have been performed, before you can drop the table. The other is the time that has to pass before a record can be deleted from the table. Be aware, when developing, to set the no drop until <n> days idle as short as possible, preferably 0, because you really cannot drop the table if this criterium is not met. And before you know it, your development database is overloaded with all kinds of tables you cannot get rid of. Not even when you try to drop the user. The only way to get rid of those objects is wait until the idle time has passed or drop the entire database.

create immutable table circuits
( circuitid number generated by default on null as identity
, name varchar2( 128 )
, location varchar2( 128 )
, country varchar2( 128 )
, constraint pk_circuit primary key ( circuitid )
)
no drop until 0 days idle
no delete until 16 days after insert
/

If you add some rows to the table, they are there forever, well, at least for the time you specified before they can be deleted or until you can drop the table.

insert into circuits
( name, location, country )
values
( 'Bahrain International Circuit' , 'Sakhir' , 'Bahrain' )
, ( 'Jeddah Corniche Circuit' , 'Jeddah' , 'Saudi Arabia' )
, ( ‘Albert Park Grand Prix Circuit’, 'Melbourne' , 'Australia' )
, ( 'Baku City Circuit' , 'Baku' , 'Azerbaijan' )
, ( 'Miami International Autodrome' , 'Miami' , 'USA' )
, ( 'Circuit de Monaco' , 'Monte-Carlo' , 'Monaco' )
, ( ‘Circuit de Barcelona-Catalunya’, 'Montmeló' , 'Spain' )
, ( 'Circuit Gilles Villeneuve' , 'Montreal' , 'Canada' )
, ( 'Red Bull Ring' , 'Spielberg' , 'Austria' )
, ( 'Silverstone Circuit' , 'Silverstone' , 'UK' )
, ( 'Hungaroring' , 'Budapest' , 'Hungary' )
, ( 'Circuit de Spa-Francorchamps' , 'Spa' , 'Belgium' )
, ( 'Circuit Park Zandvoort' , 'Zandvoort' , 'Netherlands' )
, ( 'Autodromo Nazionale di Monza' , 'Monza' , 'Italy' )
, ( 'Marina Bay Street Circuit' , 'Marina Bay' , 'Singapore' )
, ( 'Suzuka Circuit' , 'Suzuka' , 'Japan' )
, ( 'Losail International Circuit' , 'Al Daayen' , 'Qatar' )
, ( 'Circuit of the Americas' , 'Austin' , 'USA' )
, ( 'Autódromo Hermanos Rodríguez' , 'Mexico City' , 'Mexico' )
, ( 'Autódromo José Carlos Pace' , 'São Paulo' , 'Brazil' )
, ( ‘Las Vegas Strip Street Circuit’, 'Las Vegas' , 'United States' )
, ( 'Yas Marina Circuit' , 'Abu Dhabi' , 'UAE' )
/

If you try to update a row in this table you always get an error:

update circuits cir
set cir.name = 'Circuit park Zandvoort'
where cir.circuitid = 13
/
update circuits cir
*
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain or immutable table

When you try to delete a row, before the specified time has passed, you will receive the same error:

delete
from circuits cir
where cir.circuitid = 13
/
from circuits cir
*
ERROR at line 2:
ORA-05715: operation not allowed on the blockchain or immutable table

This feature was introduced in Oracle Database 21c but backported to Oracle Database 19.11

Blockchain tables
Blockchain tables go a step further than immutable tables. Besides all the constraints that come with immutable tables, blockchain table are chained together using a hashing algorithm.

hashing using sha2_512 version v1
The hashing algorithm is calculated using the existing data (a previous hash value) and the data that is added to the table. This increases the time needed to insert a row, compared to an immutable table, but this makes it harder to temper with the data in the table, using low level code, like editing the file on disks directly (not using your normal SQL interface).

This feature was introduced in Oracle Database 21c but backported to Oracle Database 19.10

Blockchain and immutable tables can be used for data that shouldn’t be tampered with, like contracts and the like. Be aware though, especially when developing, that the data really cannot be removed until the periods specified when creating the table have passed. And the structure of the table can never be modified, after it has been created. I think, in development, you should always use no drop until 0 days idle, otherwise you will end up with a lot of tables in your schema you cannot get rid of.

The source code used in this article can be found at:
https://github.com/Qualogy-Solutions/OracleDatabase23c/blob/main/immutabletables.sql

Also read my previous blogs about Oracle Database 23:

And my next blog:

SQL Domains