A blockchain table chains its rows with cryptographic hashes, so any change to stored data breaks the chain. DBMS_BLOCKCHAIN_TABLE recomputes the hashes to prove that no row was altered, and deletes rows only once their retention period has passed.
Code for This Guide
The main examples are in the examples/pkg-data-ops folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
dbms_blockchain_table.verify_rows(schema, table,
number_of_rows_verified => n_out);
dbms_blockchain_table.delete_expired_rows(schema, table,
number_of_rows_deleted => n_out);VERIFY_ROWS can also check a range of rows by instance and creation time.
Verify a Payment Ledger
PAYMENT_LEDGER keeps a tamper-evident copy of 50 payments. Rows cannot be deleted for 16 days after insert.
Example:
create blockchain table payment_ledger (
payment_id number,
amount number(10,2)
) no drop until 0 days idle
no delete until 16 days after insert
hashing using "SHA2_512" version "v2";
insert into payment_ledger select payment_id, amount from payments where payment_id <= 50;
commit;
declare
v_verified number;
v_deleted number;
begin
-- recompute the hashes of all rows and compare them with the stored chain
dbms_blockchain_table.verify_rows(user, 'PAYMENT_LEDGER',
number_of_rows_verified => v_verified);
dbms_output.put_line(v_verified || ' rows verified');
-- rows can only be deleted once their retention period has passed
dbms_blockchain_table.delete_expired_rows(user, 'PAYMENT_LEDGER',
number_of_rows_deleted => v_deleted);
dbms_output.put_line(v_deleted || ' rows deleted: none has expired yet');
end;
/Output:
Blockchain TABLE created. 50 rows inserted. Commit complete. 50 rows verified 0 rows deleted: none has expired yet PL/SQL procedure successfully completed.
VERIFY_ROWS recomputes the hash of each row and compares it with the stored chain: all 50 rows verify. DELETE_EXPIRED_ROWS removes nothing, since every row is still inside its 16-day retention period. The example drops the table afterward, which NO DROP UNTIL 0 DAYS IDLE allows.
Things to Know
- VERIFY_ROWS raises an error if a hash does not match, so a successful call is the proof.
- Blockchain tables allow only inserts; UPDATE and DELETE of unexpired rows fail.
- Rows can also be signed by users with SIGN_ROW, using their certificates, for non-repudiation.
Related Guides
Conclusion
DBMS_BLOCKCHAIN_TABLE.VERIFY_ROWS proves that the rows of a blockchain table have not been changed, and DELETE_EXPIRED_ROWS removes rows only after their retention period. Run verification regularly on tables that must be tamper-evident.
