Blockchain Tables in Oracle Database 21c

A blockchain table is a tamper-proof, insert-only table with an associated table-level and row-level retention period.

This feature has been backported to Oracle 19.10, but also requires patch 32431413 and the COMPATIBLE parameter set to 19.10. In future release update for 19c, the additional patch shouldn't be necessary.

When learning about blockchain tables, be careful not to set excessively long retention periods, or you will have to wait a long time to drop your test tables.

◉ Create a Blockchain Table

In addition to adding the BLOCKCHAIN keyword to the CREATE TABLE command, there are three blockchain clauses.

The blockchain drop table clause determines how long the table is protected from being dropped. This only affects the DROP TABLE command. The table will still be removed if a DROP USER ... CASCADE command is issued to remove the whole schema.

◉ NO DROP [ UNTIL number DAYS IDLE ]

◉ NO DROP : The table can't be dropped. Be careful about using this setting during testing.

NO DROP UNTIL number DAYS IDLE : The table can't dropped until there have been no new rows inserted for the specified number of days. You may prefer to use 0 or 1 as the number of days during testing this functionality.

The blockchain row retention clause determines how long each row will be protected from deletion.

NO DELETE { [ LOCKED ] | (UNTIL number DAYS AFTER INSERT [ LOCKED ]) }

◉ NO DELETE : Each row is retained forever. The absence of the LOCKED keyword implies the setting can be changed with the ALTER TABLE command, but it can't. Retention periods can only be increased.

◉ NO DELETE LOCKED : Same as NO DELETE.

◉ NO DELETE UNTIL number DAYS AFTER INSERT : Each row is protected from deletion for the specified number of days, but this setting can be increased using the ALTER TABLE command. Minimum 16 days.

◉ NO DELETE UNTIL number DAYS AFTER INSERT LOCKED : Each row is protected from deletion for the specified number of days, and this setting can't be changed using the ALTER TABLE command. Minimum 16 days.

The blockchain hash and data format clause is fixed in the current release. It looks like it will allow alternative hashing algorithms in future releases.

HASHING USING sha2_512 VERSION v1

Putting it all together gives us something like the following.

--drop table bct_t1 purge;

create blockchain table bct_t1 (

  id            number,

  fruit         varchar2(20),

  quantity      number,

  created_date  date,

  constraint bct_t1_pk primary key (id)

)

no drop until 0 days idle

no delete until 16 days after insert

hashing using "SHA2_512" version "v1";

Checking the USER_TAB_COLS view shows us several invisible columns have been added to our column list. The hidden columns are described here.

set linesize 120 pagesize 50

column column_name format a30

column data_type format a27

column hidden_column format a13

select internal_column_id,

       column_name,

       data_type,

       data_length,

       hidden_column

FROM   user_tab_cols       

WHERE  table_name = 'BCT_T1'

ORDER BY internal_column_id;

INTERNAL_ COLUMN_ID	COLUMN_NAME	DATA_TYPE	DATA_LENGTH	HIDDEN_ COLUMN
1	ID	NUMBER	22	NO
2	FRUIT	VARCHAR2	25	NO
3	QUANTITY	NUMBER	22	NO
4	CREATED_DATE	DATE	7	NO
5	ORABCTAB_INST_ID$	NUMBER	22	YES
6	ORABCTAB_CHAIN_ID$	NUMBER	22	YES
7	ORABCTAB_SEQ_NUM$	NUMBER	22	YES
8	ORABCTAB_CREATION_TIME$	TIMESTAMP(6) WITH TIME ZONE	13	YES
9	ORABCTAB_USER_NUMBER$	NUMBER	22	YES
10	ORABCTAB_HASH$	RAW	2000	YES
11	ORABCTAB_SIGNATURE$	RAW	2000	YES
12	ORABCTAB_SIGNATURE_ALG$	NUMBER	22	YES
13	ORABCTAB_SIGNATURE_CERT$	RAW	16	YES
14	ORABCTAB_SPARE$	RAW	2000	YES

14 rows selected.

SQL>

The {CDB|DBA|ALL|USER}_BLOCKCHAIN_TABLES views display information about blockchain tables. It's a view over the SYS.BLOCKCHAIN_TABLE$ table.

column row_retention format a13

column row_retention_locked format a20

column table_inactivity_retention format a26

column hash_algorithm format a14

SELECT row_retention,

       row_retention_locked, 

       table_inactivity_retention,

       hash_algorithm  

FROM   user_blockchain_tables 

WHERE  table_name = 'BCT_T1';

ROW_RETENTION ROW_RETENTION_LOCKED TABLE_INACTIVITY_RETENTION HASH_ALGORITHM

------------- -------------------- -------------------------- --------------

           16 NO                                            0 SHA2_512

SQL>

◉ Alter a Blockchain Table

The documentation suggests the blockchain drop table clause can be altered using the ALTER TABLE command, as long as the retention period is not reduced. At the time of writing this doesn't seem to work for tables that were initially created with NO DROP UNTIL 0 DAYS IDLE, as all values of days return an error. We currently have a retention period of 0 days for the table. In the following example we try to change it to 100 days, which gives an error. The command is syntactically correct, so I assume this is a bug in this release update.

alter table bct_t1 no drop until 100 days idle;

Error report -

ORA-05732: retention value cannot be lowered

SQL>

This command will work on tables created with NO DROP UNTIL 1 DAYS IDLE or higher.

Regardless of the current drop delay setting, an attempt to switch to the maximum value of NO DROP causes an ORA-00600 error.

alter table bct_t1 no drop;

Error starting at line : 1 in command -

alter table bct_t1 no drop

Error report -

ORA-00600: internal error code, arguments: [atbbctable_1], [0], [], [], [], [], [], [], [], [], [], []

This is a problem, as I would expect most people to want to play it safe by starting with a zero day delay, then upping the value later once they are happy with their setup. Starting on day one with a NO DROP seems very risky, as the only way to remove the table is to drop the whole schema.

Assuming it was not defined as locked, the blockchain row retention clause can be modified using the ALTER TABLE command, as long as the retention period is not reduced. We currently have a row retention period of 16 days. In the example below we increase that value to 32. When we subsequently attempt to lower the value to 16 it gives an error.

-- Increase to 32 days.

alter table bct_t1 no delete until 32 days after insert;

Table BCT_T1 altered.

SQL>

-- Decrease to 16 days (fail).

alter table bct_t1 no delete until 16 days after insert;

Error report -

ORA-05732: retention value cannot be lowered

SQL>

In the current release, attempting to set the row retention to NO DELETE, which is an increase in the retention period, results in an ORA-00600 error. I assume this is a bug in the current release update.

alter table bct_t1 no delete;

Error report -

ORA-00600: internal error code, arguments: [atbbctable_1], [0], [], [], [], [], [], [], [], [], [], []

◉ Blocked DML and DDL Operations

As you would expect for an insert-only table, all DML and DDL operations that would result in row data being amended or deleted are prevented for a blockchain table.

The following example shows a successful insert, then some unsuccessful DML statements.

-- INSERT

insert into bct_t1 (id, fruit, quantity, created_date ) values (1, 'apple', 20, sysdate);

1 row inserted.

SQL> commit;

Commit complete.

SQL>

-- UPDATE

update bct_t1 set quantity = 10 where id = 1;

Error report -

SQL Error: ORA-05715: operation not allowed on the blockchain table

SQL>

-- DELETE

delete from bct_t1 where id = 1;

Error report -

SQL Error: ORA-05715: operation not allowed on the blockchain table

SQL>

Some DDL statement that could alter the contents of the data are also prevented. Here is an example of the TRUNCATE statement.

truncate table bct_t1;

Error report -

ORA-05715: operation not allowed on the blockchain table

SQL>

Extending existing columns is fine, but adding new columns or dropping existing columns is not allowed.

-- Extend column.

alter table bct_t1 modify (fruit varchar2(25));

Table BCT_T1 altered.

SQL>

-- Add column

alter table bct_t1 add (additional_info varchar2(50));

Error report -

ORA-05715: operation not allowed on the blockchain table

SQL>

-- Drop column.

alter table bct_t1 drop column quantity;

Error report -

ORA-05715: operation not allowed on the blockchain table

SQL>

◉ DBMS_BLOCKCHAIN_TABLE Package

The DBMS_BLOCKCHAIN_TABLE package is used for maintenance of blockchain tables.

The DELETE_EXPIRED_ROWS procedure removes any rows that are beyond the retention period. They can't be removed using a normal DELETE statement.

set serveroutput on

declare

  l_rows  number;

begin

  dbms_blockchain_table.delete_expired_rows(

    schema_name            => 'admin',

    table_name             => 'bct_t1',

    before_timestamp       => null,

    number_of_rows_deleted => l_rows);

  dbms_output.put_line('Rows Deleted=' || l_rows);

end;

/

Rows Deleted=0

PL/SQL procedure successfully completed.

SQL>

The VERIFY_ROWS procedure checks the rows in the table have a consistent hash, and signature if used.

set serveroutput on

declare

  l_rows      number;

  l_verified  number;

begin

  select count(*)

  into   l_rows

  from   admin.bct_t1;

  dbms_blockchain_table.verify_rows(

    schema_name             => 'admin',

    table_name              => 'bct_t1',

    number_of_rows_verified => l_verified);

  dbms_output.put_line('Rows=' || l_rows || '  Verified Rows=' || l_verified);

end;

/

Rows=1  Verified Rows=1

PL/SQL procedure successfully completed.

SQL>

◉ Considerations

There are a number of things to consider when using blockchain tables.

◉ My overall feeling regarding the 21.1 and 21.2 releases is that the blockchain implementation is kind-of buggy at this point. There are some features that don't work as documented, resulting in error messages that are inaccurate, or aren't trapped properly. There are also some features I've not included in this article as they appear not to work at all. In one case resulting in memory failures that can only be resolved by restarting the instance. I'll revisit these over the next few release updates to see if they get resolved, and update this article accordingly.

◉ Blockchain tables are slower than conventional tables, due to the extra work associated with them.

◉ Blockchain tables can be indexed and partitioned in the normal manner.

◉ There are some restrictions associated with data pump against blockchain tables, described here.

◉ There are a number of general restrictions associated with blockchain tables, described here.

◉ Oracle recommend saving the current hash and the corresponding sequence number for each chain in the instance somewhere outside of the database. This allows you to compare your recorded values to those in the table for extra assurance.

◉ In data guard environments, Oracle recommend maximum protection mode or maximum availability mode when working with blockchain tables.

◉ User certificates can be added to the database using the ADD_CERTIFICATE procedure in the DBMS_USER_CERTS package, and applied to existing rows using the SIGN_ROW procedure in the DBMS_BLOCKCHAIN_TABLE package. This functionality does not appear to work at this point.

I guess the main question should be, why would you use a blockchain table?

◉ If you need an insert-only tamper proof table in your application generally, this could be the solution.

◉ If you want to take advantage of the trust associated with blockchain in a centralized manner, rather than having multiple client applications having to manage blockchains individually, using a blockchain table allows you to centralise that trust.

◉ You can add the trust associated with blockchain to existing applications without having to worry about recoding them.