BlockChain Tables in Oracle 21c

Blockchain tables are designed to allow insert operations only, updates and any modifications are not allowed, and delete operations are restricted. The rows are organized into chains by storing previous row’s hash values in the current row and chain of rows is verifiable by all participants.

Oracle 19c introduced Immutable tables concept, that provides protection against unauthorized data modifications. The blockchain tables concept introduced in Oracle 21c and can be backported to 19c using a patch 32431413, but COMPATIBLE parameter must be set to 19.10.0 or late.

These tables are useful to implement Blockchain applications to handle tamper-resistant blockchain transactions with verifiable crypto-secure data management practices. The blockchain tables prevent unauthorized changes or deletion by criminals, hackers and fraud and protect critical company data. Blockchain tables has hidden columns as well and these values are managed by the database.

Blockchain tables and regular tables can be used in queries and transactions, also you can create indexes and partitions.

Blockchain tables hidden columns

Column Name	Data Type	Description
ORABCTAB_INST_ID$	NUMBER (22)	Instance ID of the database instance into which the row is inserted.
ORABCTAB_CHAIN_ID$	NUMBER (22)	Chain ID of the chain, in the database instance, into which the row is inserted.  0 through 31 are valid values.
ORABCTAB_SEQ_NUM$	NUMBER(22)	Sequence number of the row on the chain
ORABCTAB_CREATION_TIME$	TIMESTAMP WITH TIME ZONE	Row created time in UTC format
ORABCTAB_USER_NUMBER$	NUMBER (22)	Database User ID who inserted the row.
ORABCTAB_HASH$	RAW(2000)	Hash value of the row
ORABCTAB_SIGNATURE$	RAW(2000)	User signature of the row
ORABCTAB_SIGNATURE_ALG$	NUMBER(22)	Signature algorithm used to produce the user signature of a signed row.
ORABCTAB_SIGNATURE_CERT$	RAW(16)	GUID of the certificate associated with the signature on a signed row.
ORABCTAB_SPARE$	RAW(2000)	Reserved for future use.

Important Guidelines for Blockchain Tables

• In case of Oracle RAC instance, a block chain table contains 32 chains and chain will have unique combination of instance ID and chain ID. It is recommended to create index on the combination of Instance ID, chain ID and sequence number.

• The SHA2-512 hashing algorithm used to handle hash value

• In case Oracle Data Guard, to avoid data loss consider using Maximum availability or Maximum protection mode

• To specify retention period for Blockchain table use “NO DROP” Clause in the CREATE BLOCKCHAIN TABLE statement to specify retention period.

• To specify retention period for rows in Blockchain table use “NO DELETE” Clause in the CREATE BLOCKCHAIN TABLE statement to specify retention period for rows

Restrictions for Blockchain tables

• There are many restrictions when using blockchain tables

• Many datatypes are not supported such as nested table , varray, REF , ROWID, UROWID, LONG, object type, BFILE, XMLType , , , TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE

• Point-in-time recovery or flashback database will undo changes on all database objects including blockchain tables.

• Blockchain Retention policies depends on system time and any changes to systems time must be audited.

• Max number of user column allowed are 980

• Blockchain tables doesn’t allow or support below operations

Column level restrictions

o Adding, dropping, and renaming columns

o Dropping partitions

Row level restrictions

o Update or merge rows

o Defining BEFORE ROW triggers that fire for update operations are not allowed.

Table level restrictions

o Truncate table

o Inserting data using parallel DML

o Sharded tables

o During distributed transactions, Inserting data into a blockchain table using Active Data Guard DML redirection is not supported

o Direct-path loading

o Flashback table

o Cannot convert a regular table to a blockchain table or vice versa.

o XA transactions

Database level restrictions

o Export and Import can be done as regular tables, without the system-generated hidden columns.

o Creating blockchain tables in CDB or application root

o Creating Oracle Label Security (OLS) policies

o Using the DBMS_REDEFINITION package for Online redefinition

o Creating Oracle Virtual Private Database (VPD) policies

o When using Transient Logical Standby and rolling upgrades, the DDL and DML on blockchain tables are not replicated and supported

o when using Logical Standby and Oracle GoldenGate, the DDL and DML on blockchain tables succeed on the primary database but are not replicated to standby databases

o Creating Automatic Data Optimization (ADO) policies

Thanks & Regards

https://oracleracexpert.com, Oracle ACE