How to use Table Blockchain in Oracle 23ai

Oracle 23ai introduces the concept of Table Blockchain, which allows you to create and manage immutable tables. These tables are particularly useful in scenarios requiring a high level of data integrity and security, such as financial transactions, audits, and compliance logging. A blockchain table in Oracle 23ai ensures immutability once data is inserted, it cannot be deleted or modified without detection. The Oracle 23ai database has the capability to detect any attempt to tamper with the data in the table. Each row is hashed and the hash is stored in the row itself. Rows are cryptographically linked, forming a chain of data, similar to a blockchain.

In this blog I will show you how to use Table Blockchain in Oracle 23ai.

Prerequisites

1    You must have a 23ai pluggable database.

2. You must have a user with access to the pluggable database.

Steps

1. Connect to the OE schema and create a blockchain table using the CREATE BLOCKCHAIN TABLE command.

SQL> create blockchain table financial sales (order_num number, day date, cust_id number, movie_id number, title varchar(25))
NO DROP UNTIL 15 days IDLE
NO DELETE UNTIL 16 days AFTER INSERT
LOCKED HASHING USING "SHA2_512" version "v2";

2. As the same user, insert records into the newly created financial_sales table and commit.

SQL>  insert into financial_sales values (00359, systimestamp, 5000, 9875, 'Avatar');

commit;

3. Open a new terminal as sysdba user and insert a row into the financial_sales table.

SQL>  INSERT INTO oe.financial_sales VALUES (00123, systimestamp, 5000, 9875,'Top Gun2');

4. As sysdba user from the new terminal, try and update the order_id that the sysdba user has just inserted. You will get an ORA-05715 error.

SQL>  UPDATE oe.financial_sales SET cust_id=5001 WHERE order_num=00123;

5. From the first session, as the OE user try and drop the table, and you will get an ORA-05723 which does not allow the user to drop the table based on the NO DROP UNTIL 15 days clause when the table was created.

SQL> DROP TABLE oe.financial_sales;