Blockchain Tables in Oracle Database 23c: Time Travel and Wormholes Part 2

Uncategorized Leave a comment

In Part 1 I demonstrated the basic functionality of blockchain tables in Oracle Database 19c, otherwise known as V1 blockchain tables. They are available if you set the COMPATIBLE parameter to 19.10.0.0 or higher, and work as advertised but have a possible vulnerability depending on how the blockchain table was created. Blockchain tables in Oracle Database 23c, also known as V2 blockchain tables, add a few new features.

Add or Drop Columns

If you create a blockchain table in 19c with an extra column you decide you don’t need and try to remove it, you can’t do much unless the table is empty or the retention period expires:

-- 19c V1
create BLOCKCHAIN table bt_descriptions
   (id           number,
    description  varchar2(100),
    extrastuff   varchar2(4000))
no drop until 10 days idle
no delete until 16 days after INSERT
hashing using SHA2_512 version v1;

Blockchain TABLE created.

insert into bt_descriptions values(1,'This is row #1','miscellaneous details');
1 row inserted.
insert into bt_descriptions values(2,'This is row #2','miscellaneous details');
1 row inserted.
insert into bt_descriptions values(3,'This is row #3','miscellaneous details');
1 row inserted.

commit;
Commit complete.

alter table bt_descriptions drop column extrastuff;

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

But for V2 blockchain tables (23c) you can drop the column:

create BLOCKCHAIN table bt_descriptions_new
   (id           number,
    description  varchar2(100),
    extrastuff   varchar2(4000))
no drop until 10 days idle
no delete until 16 days after INSERT
hashing using SHA2_512 version v2;

Blockchain TABLE created.

insert into bt_descriptions_new values(1,'This is row #1','miscellaneous details');
1 row inserted.
insert into bt_descriptions_new values(2,'This is row #2','miscellaneous details');
1 row inserted.
insert into bt_descriptions_new values(3,'This is row #3','miscellaneous details');
1 row inserted.

commit;
Commit complete.

alter table bt_descriptions_new drop column extrastuff;
Table BT_DESCRIPTIONS_NEW altered.

Notice that I had to create the table with the VERSION V2 clause, otherwise it retains the behavior of V1 blockchain tables. Also, the column is not actually dropped — it’s now a hidden column to maintain the cryptographic hash chains across the rows that formerly had that column visible and populated.

select table_name, column_name, data_type, 
   char_length, hidden_column, user_generated
from dba_tab_cols where table_name='BT_DESCRIPTIONS_NEW';

The column EXTRASTUFF has now become a hidden column with a new name. It joins the other 24 hidden columns in the table that support blockchain table features.

Blockchain Table Performance

With the extra columns in a blockchain table, how will that affect performance of queries or DML on the table, especially in an Exadata environment? What does offloading look like with a blockchain table? I’ll try to answer those questions in Part 3 along with measuring HCC improvements in 23c for better storage utilization on Exadata.

Leave a Reply