Create, Drop, Alter 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. 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

Create and Drop Blockchain table

When creating block chain you can specify retention period for Blockchain table using “NO DROP” Clause in the CREATE BLOCKCHAIN TABLE statement to specify retention period and to specify retention period for rows use “NO DELETE” Clause .

Ex:- In below example creates Blockchain_T1 table the table can be dropped until 10 days and rows can be deleted until 15 days that they were inserted.

SQL> CREATE BLOCKCHAIN TABLE Blockchain_T1 (Col1 NUMBER, Col2 VARCHAR2(48), Col3 DATE)
NO DROP UNTIL 10 DAYS IDLE
NO DELETE UNTIL 15 DAYS AFTER INSERT
HASHING USING "SHA2_512" VERSION "v1";

Table level clauses

• NO DROP – The table cannot be dropped. 

• NO DROP UNTIL x DAYS IDLE – Table cannot be dropped when the table is IDLE and no new rows created for specified X number of days or retention period

Where X is the number of days.

Row level clauses

• NO DELETE or NO DELETE LOCKED – The Rows cannot be deleted.

• NO DELETE UNTIL x DAYS AFTER INSERT – the rows cannot be deleted until x number of days they were inserted, the retention setting can be changed using ALTER TABLE command.

• NO DELETE UNTIL x DAYS AFTER INSERT LOCKED – the rows cannot be deleted until x number of days they were inserted , also retention setting cannot be changed until x number of days

Create partition on Blockchain table

In below example creates Blockchain_T1 table with partitions. The table cannot be dropped until 10 days and rows cannot be deleted until 15 days that they were inserted.

SQL> CREATE BLOCKCHAIN TABLE Blockchain_T1 (Col1 NUMBER, Col2 VARCHAR2(48), Col3 DATE)
NO DROP UNTIL 10 DAYS IDLE
NO DELETE UNTIL 15 DAYS AFTER INSERT
HASHING USING "SHA2_512" VERSION "v1"
PARTITION BY RANGE(Col3)
(PARTITION p1 VALUES LESS THAN (TO_DATE('01-31-2022','mm-dd-yyyy')),
PARTITION p2 VALUES LESS THAN (TO_DATE('02-28-2022','mm-dd-yyyy')),
PARTITION p3 VALUES LESS THAN (TO_DATE('03-31-2022','mm-dd-yyyy'))
);

You can query USER_TAB_COLS for Blockchain Table details

SQL> SELECT internal_column_id as colid
column_name
data_type,
data_length,
FROM user_tab_cols
WHERE table_name = 'BLOCKCHAIN_T1'
ORDER BY colid;

COLID COLUMN_NAME DATA_TYPE DATA_LENGTH
---------- ------------------------ ------------------------------ -----------

1 Col1 NUMBER 22
2 Col2_ VARCHAR2(48) 48
3 Col3T DATE 7
4 ORABCTAB_INST_ID$ NUMBER 22
5 ORABCTAB_CHAIN_ID$ NUMBER 22
6 ORABCTAB_SEQ_NUM$ NUMBER 22
7 ORABCTAB_CREATION_TIME$ TIMESTAMP(6) WITH TIME ZONE 13
8 ORABCTAB_USER_NUMBER$ NUMBER 22
9 ORABCTAB_HASH$ RAW 2000
10 ORABCTAB_SIGNATURE$ RAW 2000
11 ORABCTAB_SIGNATURE_ALG$ NUMBER 22
12 ORABCTAB_SIGNATURE_CERT$ RAW 16
13 ORABCTAB_SPARE$ RAW 2000

13 rows selected.

You can query {CDB|DBA|ALL|USER}_BLOCKCHAIN_TABLES views to get information about Blockchain Tables

DROP - The below example drops the table if the table has not modified for retention period defined in the Blockchain creation.

SQL> DROP TABLE Blockchain_T1 PURGE;

It is recommended to use PURGE option when dropping a Blockchain table.

 
Note that Blockchain tables cannot be create the root container and application root container.

 In below example Blockchain_T2 table creation failed as it cannot be created in root container

SQL> CREATE BLOCKCHAIN TABLE Blockchain_T2 (Col4 NUMBER, Col2 VARCHAR2(48), Col3 DATE)
NO DROP UNTIL 10 DAYS IDLE
NO DELETE LOCKED
HASHING USING "SHA2_512" VERSION "v1";

Error report -
ORA-05729: blockchain table cannot be created in root container

ALTER Blockchain Tables : The Blockchain table retention can be modified using ALTER TABLE command

In below example we increased retention for Blockchain_T1 table that it cannot be dropped until table IDLE and no new rows created for 21 days.

SQL> ALTER TABLE Blockchain_T1 NO DROP UNTIL 21 DAYS IDLE;

In below example trying to lower retention for Blockchain_T1 table to 16 and the operation failed as retention value cannot be lowered.

SQL> ALTER TABLE Blockchain_T1 NO DROP UNTIL 16 DAYS IDLE;

Error report -
ORA-05732: retention value cannot be lowered

You can also increase column length but cannot add or drop column in Blockchain tables.
SQL> ALTER TABLE Blockchain_T1 MODIFY (COL2 VARCHAR2(58));

Table BLOCKCHAIN_T1 altered.

ADD column 

SQL> ALTER TABLE Blockchain_T1 ADD (Col4 varchar2(32));

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

DROP column 
SQL> ALTER TABLE Blockchain_T1 DROP column Col2;

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

DDL and DML on Block chain

In below example we are trying to DELETE, TRUNCATE, UPDATE and MOVE the operation not allowed

DELETE Table

SQL> DELETE FROM Blockchain_T1 where Col1 = 1;

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

TRUNCATE Table

SQL> TRUNCATE TABLE Blockchain_T1;

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

UPDATE Table

SQL> UPDATE Blockchain_T1 SET Col2=“Test” WHERE id = 1;

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

DROP TABLE

SQL> DROP TABLE Blockchain_T1;

Error report -
ORA-05723: drop blockchain table BLOCKCHAIN_T1 not allowed

MOVE Table
SQL> ALTER TABLE Blockchain_T1 move tablespace Blockchain_TBS2 ;

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

Pls refer  Restrictions for Blockchain tables for more details.

Thanks & Regards
https://oracleracexpert.com, Oracle ACE

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