Blockchain Tables in Oracle AI Database 26ai: A Bank Ledger Even the DBA Cannot Change, Measured

 

Tested on: Oracle AI Database 26ai (23.26.4.1.0) on Oracle Autonomous AI Database | Level: Intermediate

Every bank needs a transaction history that nobody can quietly change, not an application bug, not a compromised account, and not a DBA. Oracle AI Database 26ai offers two table types built for this: immutable tables (insert-only) and blockchain tables (insert-only, with every row cryptographically chained to the one before it). I loaded 205,000 fictional transactions into a normal table, an immutable table and a blockchain table, then tried to change them as ADMIN. Every UPDATE, DELETE and TRUNCATE on the protected tables failed with ORA-05715, and verifying the whole blockchain took 1.9 seconds. The protection has a price, though: a bulk load was 23× slower on the blockchain table, and one setting I used for the lab would make the whole ledger easy to remove in production.

Results at a glance

Test	Normal	Immutable	Blockchain
Bulk insert 100,000 rows (average of 2 rounds)	0.62 s	0.78 s	14.31 s
5,000 single-row inserts, commit after each	0.54 s	0.81 s	2.38 s
Storage for 205,000 rows	9 MB	14 MB	40 MB
UPDATE / DELETE / TRUNCATE as ADMIN	Allowed	ORA-05715	ORA-05715
Verify all 205,000 rows	n/a	n/a	1.9 s

Immutable vs blockchain: what is the difference?

Both table types only accept inserts. Rows cannot be updated, and they cannot be deleted until a retention period you define has passed. The difference is proof:

• Immutable table: the database refuses changes. It protects against mistakes and against users who go through SQL.
• Blockchain table: the database refuses changes and stores a hash of every row that includes the previous row's hash. If anyone ever altered a row outside SQL, the chain would no longer verify. This gives auditors something they can check, not just a promise.

Step 1: create the three tables

CREATE TABLE bc_heap (
  txn_id NUMBER, account_id NUMBER, amount NUMBER(12,2),
  txn_type VARCHAR2(10), txn_time TIMESTAMP
);

CREATE IMMUTABLE TABLE bc_immutable ( ...same columns... )
  NO DROP UNTIL 0 DAYS IDLE
  NO DELETE UNTIL 16 DAYS AFTER INSERT;

CREATE BLOCKCHAIN TABLE bc_ledger ( ...same columns... )
  NO DROP UNTIL 0 DAYS IDLE
  NO DELETE UNTIL 16 DAYS AFTER INSERT
  HASHING USING "SHA2_512" VERSION "v2";

16 days is the shortest row retention Oracle allows. The data dictionary confirms the settings:

TABLE_NAME ROW_RETENTION TABLE_INACTIVITY_RETENTION HASH_ALGORITHM TABLE_VERSION MAX_SYSTEM_CHAINS
---------- ------------- -------------------------- -------------- ------------- -----------------
BC_LEDGER             16                          0 SHA2_512       V2                           32

Step 2: what even ADMIN cannot do

I connected as ADMIN, the most privileged user on Autonomous Database, and tried to change transaction 42 in every table:

STEP                                TAB          RESULT
----------------------------------- ------------ -------------------------------------------------------------------
Change an amount (UPDATE)           BC_HEAP      ALLOWED (1 rows, rolled back)
Remove a transaction (DELETE)       BC_HEAP      ALLOWED (1 rows, rolled back)
Change an amount (UPDATE)           BC_IMMUTABLE ORA-05715: operation not allowed on the blockchain or immutable table
Remove a transaction (DELETE)       BC_IMMUTABLE ORA-05715: operation not allowed on the blockchain or immutable table
Change an amount (UPDATE)           BC_LEDGER    ORA-05715: operation not allowed on the blockchain or immutable table
Remove a transaction (DELETE)       BC_LEDGER    ORA-05715: operation not allowed on the blockchain or immutable table
Empty the table (TRUNCATE)          BC_IMMUTABLE ORA-05715: operation not allowed on the blockchain or immutable table
Empty the table (TRUNCATE)          BC_LEDGER    ORA-05715: operation not allowed on the blockchain or immutable table
Raise row retention to 17 days      BC_LEDGER    ALLOWED
Lower row retention back to 16 days BC_LEDGER    ORA-05732: retention value cannot be lowered

Two results matter most for a bank. First, privileges do not help: ADMIN is blocked exactly like an application user. Second, retention only moves one way. It can be raised, but ORA-05732 stops anyone from lowering it to delete rows sooner. The official purge procedure, DBMS_BLOCKCHAIN_TABLE.DELETE_EXPIRED_ROWS, deleted 0 rows because none were older than the retention.

Step 3: inside the chain

Oracle adds hidden columns to every row of a blockchain table. They are not returned by SELECT *, but you can query them by name:

SELECT txn_id, amount,
       orabctab_chain_id$      AS chain_id,
       orabctab_seq_num$       AS seq_num,
       orabctab_creation_time$ AS created,
       SUBSTR(RAWTOHEX(orabctab_hash$), 1, 32) || '...' AS row_hash
FROM   bc_ledger
WHERE  txn_id IN (1, 2, 3, 200001, 200002);

TXN_ID   AMOUNT CHAIN_ID SEQ_NUM ROW_HASH
------ -------- -------- ------- -----------------------------------
     1 45107.06        8       1 FB8AA157F2A829BB1E1A4F12BFA27BBD...
     2 37340.26        8       2 3FCE82104A51ED210FD53ED91C6248C8...
     3 35819.28        8       3 2339E8D4A631D5AA301129B015E0CCC9...
200001  8796.71        8  200001 1372BCA7B178FA4B0BECFB43EEF0C1AE...
200002 46602.42        8  200002 B2484EC5EFABAD2988578273FD59CFB2...

The table can hold up to 32 chains per instance. In my single-session test all 205,000 rows went into one chain (chain 8), numbered 1 to 205,000 with no gaps. I did not test concurrent sessions here, so I cannot say from this run how rows spread across chains under parallel load.

Step 4: verify the chain

DECLARE
  l_rows NUMBER;
BEGIN
  DBMS_BLOCKCHAIN_TABLE.VERIFY_ROWS(
    schema_name             => USER,
    table_name              => 'BC_LEDGER',
    number_of_rows_verified => l_rows);
  DBMS_OUTPUT.PUT_LINE(l_rows || ' rows verified');
END;
/

All 205,000 rows were verified in 1.9 seconds, about 108,000 rows per second. That is fast enough to run as a scheduled audit check, for example every night, and to keep the output as evidence for auditors.

Step 5: the price of protection

The immutable table cost little: 1.3× the normal table's time for a bulk load and 1.5× for single-row commits. The blockchain table is a different story, because every row must be hashed together with the previous row's hash:

• Bulk load: 100,000 rows took 14.3 seconds on average against 0.62 seconds for the normal table, 23× slower.
• OLTP style: 5,000 single-row inserts with a commit after each took 2.38 seconds against 0.54 seconds, 4.4× slower. Per transaction that is about 0.48 ms instead of 0.11 ms, so the extra cost is roughly 0.4 ms per commit.
• Storage: the hidden columns, including a 64-byte SHA-512 hash per row, made the table 40 MB against 9 MB, 4.4× larger.

For a ledger where each business transaction is inserted and committed on its own, 0.4 ms per commit is usually acceptable. For large batch loads, such as a close-of-business job inserting millions of entries, plan for the extra time and test it with your real volumes first.

The setting that would undo it all

At the end of the lab, my clean-up script dropped all three tables, and it worked, even though both protected tables were full of rows that could not be deleted for 16 days. That is because I created them with NO DROP UNTIL 0 DAYS IDLE, which allows the table to be dropped as soon as no rows have been inserted for zero days. It is the right choice for a lab and the wrong one for production. For a real ledger:

• Use NO DROP or a long idle period such as NO DROP UNTIL 365 DAYS IDLE, so the table itself cannot be removed.
• Use NO DELETE LOCKED or a retention that matches your regulatory requirement, and lock it.
• Remember that these settings can be made stricter later but not weaker, so agree on them before go-live.

When to use which

• Normal table: data that legitimately changes, such as balances and customer details.
• Immutable table: insert-only history where protection against changes through SQL is enough, with almost no overhead.
• Blockchain table: records that must be provably untouched for auditors or regulators, such as payment journals and audit trails, where the insert cost is acceptable.

Clean-up

DROP TABLE bc_heap PURGE;
DROP TABLE bc_immutable PURGE;   -- works only because of NO DROP UNTIL 0 DAYS IDLE
DROP TABLE bc_ledger PURGE;

Summary

Oracle AI Database 26ai blocked every attempt to update, delete or truncate the immutable and blockchain tables, even as ADMIN, and refused to lower the retention period. The blockchain table also proved its own integrity: 205,000 rows were verified in 1.9 seconds. The cost is real but predictable: about 0.4 ms extra per committed transaction, 23× slower bulk loads and 4.4× the storage. Choose the drop and retention settings carefully, because a lab-friendly setting like NO DROP UNTIL 0 DAYS IDLE leaves the whole ledger one DROP TABLE away.

Lab design, testing and all results are my own, run on an Oracle LiveLabs sandbox. 

Tamilselvan Perumal is an Oracle ACE Apprentice and Senior DBA with 12+ years in banking databases, working on database performance and benchmarking. Connect on LinkedIn.