How to Create Blockchain Tables in Oracle

An immutable table stops changes through SQL. A blockchain table adds proof: each row's cryptographic hash covers its data and the previous row's hash, forming a chain. Any change to the stored data, even one made outside SQL, breaks the chain and can be detected. It suits ledgers and records that must be provably unaltered.

Code for This Guide

The main examples are in the examples/special-tables folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

create blockchain table table (...)
  no drop [until n days idle]
  no delete [locked | until n days after insert]
  hashing using "SHA2_512" version "v2"

The NO DROP and NO DELETE clauses work as for immutable tables. The hashing clause is required.

Create a Ledger

Example:

create blockchain table payment_ledger (
  payment_id number,
  amount     number(10,2),
  paid_at    timestamp
) no drop until 0 days idle
  no delete until 16 days after insert
  hashing using "SHA2_512" version "v2";

insert into payment_ledger
select payment_id, amount, paid_at from payments where payment_id <= 3;
commit;

select payment_id, amount, orabctab_chain_id$ as chain, orabctab_seq_num$ as seq,
       length(orabctab_hash$) as hash_bytes
from   payment_ledger
order  by seq;

Output:

Blockchain TABLE created.

3 rows inserted.

Commit complete.

   PAYMENT_ID    AMOUNT    CHAIN    SEQ    HASH_BYTES
_____________ _________ ________ ______ _____________
            1    513.01        6      1           128
            2    255.73        6      2           128
            3    662.86        6      3           128

Each row gets hidden columns that hold the chain it belongs to, its sequence number within the chain, and its hash, 128 bytes of SHA2-512 here. Oracle keeps several chains; these three rows, inserted in one transaction, share chain 6 and are numbered 1 to 3. Drop the table afterward with drop table payment_ledger purge.

The Hidden Columns

Example:

select table_name, row_retention, hash_algorithm
from   user_blockchain_tables
where  table_name = 'LEDGER2';

select column_name, hidden_column
from   user_tab_cols
where  table_name = 'LEDGER2' and column_name like 'ORABCTAB%'
order  by column_name
fetch  first 5 rows only;

Output:

TABLE_NAME       ROW_RETENTION HASH_ALGORITHM
_____________ ________________ _________________
LEDGER2                     16 SHA2_512

COLUMN_NAME                                   HIDDEN_COLUMN
_____________________________________________ ________________
ORABCTAB_CHAIN_ID$                            YES
ORABCTAB_COUNTERSIGNATURE$                    YES
ORABCTAB_COUNTERSIGNATURE_ALG$                YES
ORABCTAB_COUNTERSIGNATURE_CERT$               YES
ORABCTAB_COUNTERSIGNATURE_ROW_FORMAT_FLAG$    YES

USER_BLOCKCHAIN_TABLES shows the retention and the hash algorithm. The ORABCTAB columns are hidden: they do not appear in SELECT *, but can be named, as the example above does.

Things to Know

• DBMS_BLOCKCHAIN_TABLE.VERIFY_ROWS checks the hashes and reports any row whose data no longer matches its chain.
• Users can sign their rows with a certificate, which records who inserted them.
• Inserts cost more than in an ordinary table, since each row is hashed; use blockchain tables for records that need proof, not for all data.

Related Guides

• How to Create Immutable Tables in Oracle

Conclusion

Blockchain tables are insert-only tables whose rows are chained by cryptographic hashes, so tampering can be detected. Create them with NO DROP, NO DELETE, and a hashing clause, and verify their integrity with DBMS_BLOCKCHAIN_TABLE.