Blockchain tables in Oracle Database 20c

by

Blockchain is a very popular and new data paradigm. Oracle makes it easy for applications to use Blockchain technology to help identify and prevent fraud.

In Database 20c, new type of table called Blockchain table. This looks like normal table except as the rows are inserted into Blockchain Table, there are cryptographically chained together. This happens automatically. The chain can be verified and signed by participants.

Blockchain Tables are very simple to integrate into applications because they look like standard database tables and use the same declarative SQLs. We can run full analytics and transactions on blockchain data.

Here is a simple example.

  1. We have to use “BLOCKCHAIN” key word while creating blockchain table in CREATE TABLE statement. It does cryptographic automatically.

CREATE BLOCKCHAIN TABLE order_ledger(Order_Id NUMBER,
Customer_Name VARCHAR2(100),
Order_Date DATE,
Order_Value NUMBER);

2. Data can be easily inserted using standard SQL commands

INSERT INTO order_ledger VALUES(1,’SHELL’,SYSDATE,100000);

3. Update and Delete DML operations are not allowed on the blockchain table. Even TRUNCATE TABLE command can’t be used with blockchain tables.

DELETE order_ledger WHERE order_id = 1;

Returns an error

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

4. Can’t drop the blockchain table.

Drop blockchainTable order_ledger;

ORA-05723: drop blockchain table order_ledger not allowed.

Even you can’t drop the tablespace containing blockchain table.

5. DBA_BLOCKCHAIN_TABLES is the most important database view related to blockchain tables in Oracle DB 20c

6. Below additional operations are not allowed with blockchain tables

  • Adding, Dropping and renaming the column(s)
  • Dropping partition
  • Inserting data using parallel DML
  • Defining BEFORE ROW trigger
  • Converting a regular table to blockchain table or vice versa

7. Can verify that no one has attempted to temper with any of the data using new pl/sql pkg

dbms_blockchain_table.verify_rows(‘BCHAIN’,’ORDER_LEDGER’,number_of_rows_verified=> v_row)

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

Blockchain tables are insert-only tables that organize rows into a number of chains. Looks like a normal table, simple to use, transact like a normal table but get the benefits of fraud detection of blockchain.