Blockchain tables in 21c? A word of warning

As I write this, the moment you do a browser search for “blockchain”, the last thing you will get a match on is the technology elements. Instead, my results are flooded with hedge funds, short selling, Gamestop and bitcoin. A friend has been sending me messages along the lines of “Is it true Satoshi Nakamoto lives in a mountain cave surrounded by computers and wild yaks?”. Yes indeed, the 2021 world looks like following 2020’s footsteps of being a crazy place

Because of this, I have little doubt that with the availability of blockchain tables in the Oracle Database, people are very keen to jump into the technology and start tinkering. Which brings me to this:

STOP…RIGHT…NOW

That’s right. Please do not create a blockchain table until you have read just a little further. It might save you a great deal of heartache.

Blockchain tables first arrived in the 20c preview release, and we call these things preview releases for a reason! It is to give you a glimpse of functionality and also to get feedback from the community in order to firm up the functionality before full production availability. In the first 20c release, when you created a blockchain table, the syntax would look like this:

SQL> create blockchain table bc1 (d date, amt number)
  2      no drop until 16 days idle
  3      no delete until 31 days after insert
  4      hashing using "sha2_512" version v1;

Table created.

Notice that 16 days? That was the minimum setting. Once you created a blockchain table and put even just a single row into it, that table was going to be there for 16 days. If you wanted to drop that table – then you were in for a lot of pain and effort. I did a video on that which you can see below

We took your feedback and adjusted that minimum threshold down to zero days for the production implementation of blockchain tables. Thus if you created a blockchain table with the a NO DROP UNTIL 0 DAYS clause, then you could drop that table as soon as you no longer needed it. Perfect for development and prototyping.

However, this does not prohibit anyone from still specifying a non-zero value, and the moment you do that, the table is there to stay!

SQL> create blockchain table bc1 (d date, amt number)
  2      no drop until 1 days idle
  3      no delete until 31 days after insert
  4      hashing using "sha2_512" version v1;

Table created.

SQL> insert into bc1 values (sysdate,1);

1 row created.

SQL> drop table bc1 purge;
drop table bc1 purge
           *
ERROR at line 1:
ORA-05723: drop blockchain table BC1 not allowed

For a true production blockchain table, that inability to meddle with the table and its data is exactly what you want. But for non-production environments, if you run a continuous integration methodology, or even just drop/recreate tables as part of unit tests, then not being able to drop a table, or even the schema that owns it, or even the tablespace it sits in, might cause you a lot of frustration.

Hence the reason for this blog post. Before you create a single blockchain table in your database, give some serious consideration to the following parameter:

SQL> show parameter max_no_drop

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
blockchain_table_max_no_drop         integer     

It sets the upper limit on how long a NO DROP clause can be, and defaults to null. Setting it to zero will ensure that no-one gets to lump a blockchain table into your schema that cannot be dropped.

SQL> alter system set blockchain_table_max_no_drop = 0 scope=both;

System altered.

SQL> create blockchain table bc2 (d date, amt number)
  2      no drop until 1 days idle
  3      no delete until 31 days after insert
  4      hashing using "sha2_512" version v1;
create blockchain table bc2 (d date, amt number)
*
ERROR at line 1:
ORA-05747: maximum retention time too high, should be less than or equal to 0 days

Note that any blockchain table you created before altering the setting are unchanged – you will need to wait for them to expire before you can drop them.

Blockchain tables are robust and tamperproof, but sometimes in your development area, a little bit of tampering is exactly what you need

Footnote: The sacrifices I make for my readers. I’m now waiting until tomorrow so I can drop that BC1 table