Oracle 21c New Feature - Blockchain Tables

This blog describes one of the hottest new features in Oracle 21c that most of you won't use: the Oracle Blockchain Table.

Getting to Know Blockchain Tables

Oracle Database 21c introduced the Oracle Blockchain Table, which is generally used in centralized ledgers and audit trails, and other such data stores that cannot tolerate tampering.  A blockchain table is append-only and tamper resistant due to special sequencing and chaining algorithms.  Rows can be appended to the end of the table, but rows cannot be updated.  Deletes can either be disabled entirely or allowed for rows beyond an age threshold.

The Oracle Blockchain Table was first announced by Larry Ellison at Oracle OpenWorld 2019 when describing the Oracle Autonomous Database.  It was first released as part of Oracle 20c, which was recalled and re-released as Oracle 21c.  Oracle Blockchain Table is now a free feature in all editions.

But, this isn't really Oracle's first foray into blockchain.  The Oracle Blockchain Platform went public in July 2018 and had been available to beta testers for several months before then.  That's great, but not everyone who needs a complete platform of blockchain capabilities.  The Oracle Blockchain Table is like one piece extracted from the platform and integrated into Oracle Database.

Overall, the blockchain table is just one of eight types of tables supported in Oracle 21c.  The eight types of tables are regular heap, regular object, index-organized, global temp, private temp, sharded, duplicated, and blockchain.  All of these are created using a CREATE TABLE statement, but the special ones like blockchain require an optional modifier as shown in the below syntax diagram:

The Oracle CREATE TABLE Statement

So, here's the basics on how a blockchain table works in Oracle 21c.  Each row in a chain, except the first row, is chained to the previous row in the chain using a cryptographic hash (SHA2-512).  The hash is based on the data in the current row plus the hash value of the previous row in the chain.  The hash is stored in the row's metadata.  The hash can be used to verify a row has not been tampered with, because as soon as the data changes the hash computes to a different value. 

Blockchain tables support queries, transactions, indexing, partitioning, Data Pump, Data Guard, RAC, and RMAN.  Well, there are a lot of restrictions noted in the Oracle 21c documentation.  For instance, several data types are unsupported, and blockchain tables do not support table clusters, cannot be index-organized tables, cannot be external-organized tables, and cannot be nested.

Concurrency is an interesting topic.  When multiple users are simultaneously inserting rows into a blockchain table, the order in which the rows are added is based on the transaction commit and not when the transaction started.  That is, even if user A starts adding a row first, user B committing first results in row B being added to the blockchain ahead of row A.

Security is no less interesting.  X.509 digital certificates can optionally be used to sign a row in a blockchain table.  Each user or application can sign the row they inserted using their own digital certificate.  While this enhances accountability, it also increases complexity and processing times.  For example: during data validation all digital certificates related to all rows must be examined.  All of those digital certificates would need to be stored as BLOBs in the database to support online verifications.  To add a digital signature to the database, use the Oracle supplied procedure DBMS_USER_CERTS.ADD_SIGNATURE.

Creating Blockchain Tables

Blockchain tables are created using the CREATE BLOCKCHAIN TABLE statement.  They can be created in a PDB, but not in the CDB$ROOT or an application container's root.  The below example of creating a blockchain table comes from the Oracle documentation.  The NO DROP clause prevents this table from being dropped unless it has been idle (no user activity) for at least 31 consecutive days.  The NO DELETE clause prevents rows from being deleted even by a superuser.

CREATE BLOCKCHAIN TABLE bank_ledger
 ( bank VARCHAR2(128), deposit_date DATE, deposit_amount NUMBER )
  NO DROP UNTIL 31 DAYS IDLE
  NO DELETE LOCKED
  HASHING USING "SHA2_512"
  VERSION "v1";

Columns are limited to scalar data types likes the ones shown above.  You cannot use BFILE, LONG, ROWID, TYPE, or other such data types.  The table also cannot have a column of type TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE, which is funny because every blockchain table has a hidden Oracle column of type TIMESTAMP WITH TIME ZONE.

Oracle always appends 10 predefined columns to each new blockchain table.  That is, every row has 10 hidden columns used internally by Oracle.  These hidden columns have names starting with ORABCTAB.  You can look at them, but not alter or remove them.

The CREATE BLOCKCHAIN TABLE statement has four mandatory table-level clauses:  NO DROP, NO DELETE, HASHING, and VERSION.  These four clauses have no defaults, so you must remember to add them.

  • The NO DROP clause is the table retention policy.
  • The NO DELETE clause is the row retention policy.
  • The HASHING clause tells it which algorithm to use when computing hashes.  Initially, the only allowable value is SHA2_512 as shown in the above example.
  • The VERSION clause is said to be for future use with Oracle GoldenGate, and for now it should just be set to v1 as shown in the above example.

Specifying a tablespace is optional.  If you don't specify a tablespace when creating a blockchain table, it will be created in the default tablespace of the table owner.  You cannot drop a tablespace containing a blockchain table, even if the blockchain table is empty.  You might therefore want a separate tablespace for blockchain tables.

There is one init.ora parameter related to creating blockchain tables: the database initialization parameter BLOCKCHAIN_TABLE_MAX_NO_DROP sets the maximum value you can specify in the NO DROP clause of the CREATE BLOCKCHAIN TABLE statement.  In other words, it defines the maximum allowed value of N that can be set in the statement CREATE BLOCKCHAIN TABLE … NO DROP UNTIL N DAYS IDLE. 

Working with Blockchain Tables

There are two main ways to work with blockchain tables in Oracle 21c: SQL statements and the PL/SQL API DBMS_BLOCKCHAIN_TABLE.  SQL statements allow you to create, alter, and drop blockchain tables within the many restrictions noted herein.  The API is generally used to maintain digital row signatures, but also allows you to inspect the blockchain table.

Listing and monitoring Oracle Blockchain Tables is performed using the Oracle supplied views DBA|ALL|USER_BLOCKCHAIN_TABLES.

Once created you cannot alter the structure of a blockchain table: you cannot add, drop, or rename columns, and you cannot drop partitions.  The ALTER BLOCKCHAIN TABLE syntax only allows you to alter the NO DROP and NO DELETE clauses, but not the table structure.  You cannot rename a blockchain table or any of its columns.

Blockchain tables cannot be converted to a regular table.

To drop a blockchain table it must meet the IDLE condition set in the table definition.  In the earlier example of the CREATE BLOCKCHAIN TABLE statement, the IDLE condition was NO DROP UNTIL 31 DAYS IDLE.  This means you cannot drop the table until 31 days have passed since the last DML operation.

You cannot drop a tablespace containing a blockchain table.  If you run the command DROP TABLESPACE FOO INCLUDING CONTENTS AND DATAFILES, then Oracle will raise an error and the tablespace will not be dropped.  You can, however, move the blockchain table to a new tablespace and then drop the old tablespace.

If you need to dump a blockchain table, then you must use Data Pump Export in conventional mode and most export options are disabled.  For a complete and current list of restrictions see the Oracle Database Utilities guide (ref).

Digital signatures were mentioned earlier.  After receiving a certificate from a Certificate Authority (CA) you can add it to the database as a BLOB record, and then use it to digitally sign rows using the Oracle supplied procedure DBMS_BLOCKCHAIN_TABLE.SIGN_ROW.  A complete example can be found in the Oracle online documentation (ref).

Comments

Post a Comment

Popular posts from this blog

Using DBGen to Generate TPC-H Test Data

Image
In this post I describe the Data Base Generator (DBGen) utility in terms of what it is, how to install it, and how to use it.   This post assumes a Linux operating system.   There are different build procedures for DBGen on Windows, but the concepts carry over. DBGen is used to create a TPC-H schema (i.e., it provides the CREATE TABLE statements) and to generate the data.   DBGen is used in official TPC-H benchmarking, but it can also be used in informal TPC-H-like benchmarking.   The schema and data is compatible with most 3 rd party benchmarking tools like HammerDB, so even if you plan to run a TPC-H-like test using HammerDB you may still use DBGen to create the schema and data. The  CREATE TABLE   statements are ANSI standard SQL and can be run in any DBMS without editing.   There are no SQL statements for indexes or foreign key constraints provided by DBGen since those types of objects are not required.   The data is created as delimited...
Read more

Oracle 21c Caching Solutions

This blog post reviews all of the caching solutions available in Oracle Database 21c.  I dive into several of the caching solutions, but I do try to keep things relatively short-and-sweet here.  I have other blogs dedicated to each caching solution. I don't mean the Oracle SGA or PGA.   I mean optional features you can enable to enhance performance. The content of this blog post is applicable to Oracle 12g, 18c, 19c, and 21c.   Some of the caching solutions were available in Oracle 11g, but I did not regression test my examples back that far. There have been no new caching solutions in Oracle 12g R2 through 21c.   One solution in particular, the In-Memory Column Store, has received some major enhancements.   I briefly describe it here, but the deep dive is in another blog post. Summary of Oracle Caching Solutions Oracle 21c includes six caching features as listed in my table below.   I've listed them in order from newest on top to oldest at t...
Read more

TPC-like Database Benchmarking Tools

Image
  This post describes five popular tools for benchmarking relational databases: HammerDB, Quest Benchmark Factory, SwingBench, pgbench, and Oracle RAT.  Previously, I described TPC and TPC-like benchmarks for relational databases.   Recall a benchmark is "like" a TPC benchmark if it follows some of the rules but not all of them.   The tools described in this post follow some of the TPC's rules so we call them "TPC-like".   Publishing results is generally not allowed by commercial DBMS vendors, and use of the benchmark trademark is also restricted. Ok then, let's look at the most commonly used benchmarking tools for relational databases. HammerDB is my #1 choice for benchmarking relational databases.   HammerDB supports every major DBMS on Linux and Windows including bare metal, VM, and cloud.   This means you can use one tool for all environments.   You can even compare performance across platforms or DBMS, such as comparing Oracle on Linux...
Read more