Blockchain tables in Oracle Database 20c

Blockchain tables are insert-only tables that organize rows into a number of chains and is a new concept starting with Oracle 20c. Each row in a chain, except the first row, is chained to the previous row in the chain by using a cryptographic hash. For each Oracle RAC instance a blockchain table contains thirty two chains, ranging from 0 through 31.

This is an example of how a blockchain table is created in 20c:

Let me first point out the main restrictions:

– Blockchain tables cannot be created in the root container and in an application root container: ORA-05729: blockchain table cannot be created in root container

– You cannot update the rows: ORA-05715: operation not allowed on the blockchain table

– In general, you cannot delete rows, truncate the table or drop the blockchain table: ORA-05723: drop blockchain table NDA_RECORDS not allowed

– Don’t even try to drop the tablespace containing blockchain tables, here is what happens:

DROP TABLESPACE BC_DATA INCLUDING CONTENTS AND DATAFILES;

ORA-00604: error occurred at recursive SQL level 1

ORA-05723: drop blockchain table NDA_RECORDS not allowed

The most important new view in 20c related to blockchain tables is DBA_BLOCKCHAIN_TABLES:

The 4 (non-trivial) columns of DBA_BLOCKCHAIN_TABLES contain the following information:

1. ROW_RETENTION: The minimum number of days a row must be retained after it is inserted into the table – if the value of this column is NULL, then rows can never be deleted from the table. In the example above, the row can be deleted after 16 days. Otherwise, you will get: ORA-05715: operation not allowed on the blockchain table

2. ROW_RETENTION_LOCKED: 2 possible values (YES and NO) showing if the row retention period for the blockchain table is locked.

YES: The row retention period is locked. You cannot change the row retention period.

NO: The row retention period is not locked. You can change the row retention period to a value higher than the current value with the SQL statement ALTER TABLE … NO DELETE UNTIL n DAYS AFTER INSERT.

3. TABLE_INACTIVITY_RETENTION: Number of days for which the blockchain table must be inactive before it can be dropped, that is, the number of days that must pass after the most recent row insertion before the table can be dropped. A table with no rows can be dropped at any time, regardless of this column value. In the example above, a year of inactivity must pass before the table can be dropped.

4. HASH_ALGORITHM: The algorithm used for computing the hash value for each table row.

To each row you add/insert to the blockchain table, Oracle adds values to the hidden columns of the blockchain table. Hidden columns are populated after you commit. They are used to implement sequencing of rows and verify that data is tamper-resistant. You can create indexes on hidden columns. In order to view the values of the hidden columns, you should explicitly include their names in the SQL, just like this:

Hidden Columns in Blockchain Tables will give you more details about the subject.

The following additional operations are not allowed with blockchain tables:

– Adding, dropping, and renaming columns

– Dropping partitions

– Defining BEFORE ROW triggers that fire for update operations (other triggers are allowed)

– Direct-path loading

– Inserting data using parallel DML

– Converting a regular table to a blockchain table (or vice versa)

There is a new PL/SQL procedure DBMS_BLOCKCHAIN_TABLE which contains 5 procedures, one of which VERIFY_ROWS is used to validate he data in the blockchain table.

Use DBMS_BLOCKCHAIN_TABLE.DELETE_EXPIRED_ROWS to remove rows that are beyond the retention period of the blockchain table.

For DBAs:

– For each chain in a database instance, periodically save the current hash and the corresponding sequence number outside the database.

– In an Oracle Data Guard environment, consider using the maximum protection mode or maximum availability mode to avoid loss of data.

You can use certificates to verify the signature of a blockchain table row. Check here on how to add and delete certificates to blockchain table rows.