First, to explore blockchain table in 19c release, you will need to apply the latest patches and change compatibility parameter value (which is unusual), the blockchain feature was introduced initially in 21c and back-ported to 19c with 19.10 RU (release update) in January 2021 CPU. Here I am going to explore it after patching the database to 19.11 (April 2021 CPU).

To raise the compatibility parameter:

I have shutdown the database

SQL> shutdown immediate;

Then, edited the init.ora file and changed compatibility parameter value:

compatible=’19.11.0′

Then, I started up the database:

SQL> startup pfile=’D:\WINDOWS.X64_193000_db_home\dbs\init.ora’

Then, checking the compatibility parameter value:

SQL> SELECT name, value, description FROM v$parameter WHERE name = ‘compatible’;

Let us explore it:

In pluggable database PDB1

CREATE TABLESPACE ledgertbs;

CREATE USER auditor identified by audit_123 DEFAULT TABLESPACE ledgertbs;

GRANT create session, create table, unlimited tablespace TO auditor;

GRANT execute ON sys.dbms_blockchain_table TO auditor;

Connecting to PDB1 database as user “auditor”:

I will create a blockchain table named AUDITOR.LEDGER_EMP that will maintain a tamper-resistant ledger of current and historical transactions in PDB1. Rows can never be deleted in the AUDITOR.LEDGER_EMP blockchain table. The blockchain table can be dropped only after 3 days of inactivity.

SQL> CREATE BLOCKCHAIN TABLE ledger_emp (employee_id NUMBER, employee_name varchar2(15), salary NUMBER)

                     NO DROP UNTIL 3 DAYS IDLE

                     NO DELETE LOCKED

                     HASHING USING “SHA2_512” VERSION “v1”;

Checking user_blockchain_tables to list blockchain table information:

SQL> SELECT *  FROM   user_blockchain_tables ;

I will insert 1 record:

SQL> INSERT INTO ledger_emp VALUES (106,’EMAD’,3000);

SQL> COMMIT;

If I attempt to drop the table:

SQL> drop table ledger_emp;

ORA-05723: drop blockchain or immutable table LEDGER_EMP not allowed

If I attempt to delete all records from the table:

SQL> delete from auditor.ledger_emp;

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

To inspect the row hash value:

SELECT ORABCTAB_CHAIN_ID$ “Chain ID”, ORABCTAB_SEQ_NUM$ “Seq Num”,

               to_char(ORABCTAB_CREATION_TIME$,’dd-Mon-YYYY hh-mi’) “Chain date”,

               ORABCTAB_USER_NUMBER$ “User Num”, ORABCTAB_HASH$ “Chain HASH”,

               employee_id, salary

        FROM   ledger_emp;

There is a parameter called BLOCKCHAIN_TABLE_MAX_NO_DROP

basically this parameter controls the amount of idle time the table must be inactive before

it can be dropped, this parameter is useful if you want to protect from anyone by mistake setting the nodrop option to very high value.

the default value is NONE, based on documentation:

https://docs.oracle.com/en/database/oracle/oracle-database/21/refrn/BLOCKCHAIN_TABLE_MAX_NO_DROP.html#GUID-26AF15B2-5621-4602-AA6E-D92842E4285C

from PDB level:

sqlplus / as sysdba

SQL> alter session set container=PDB1;

SQL> alter system set blockchain_table_max_no_drop=0;

alter system set blockchain_table_max_no_drop=0

*

ERROR at line 1:

ORA-01031: insufficient privileges

from CDB level:

sqlplus / as sysdba

SQL> alter system set blockchain_table_max_no_drop=0;

System altered.

Even though, its stated in the documentation that you can modify it from PDB level ?! (is this a documentation bug)

select

    name,type,value,ISPDB_MODIFIABLE,ISINSTANCE_MODIFIABLE,ISMODIFIED,ISSYS_MODIFIABLE,DESCRIPTION

from

    v$parameter

where

name=’blockchain_table_max_no_drop’;

Now, after setting the parameter to 0 if you attempt to create the block-chain table with number of days more than zero:

SQL> CREATE BLOCKCHAIN TABLE auditor.ledger_emp_n (employee_id NUMBER, employee_name varchar2(15), salary NUMBER)

                         NO DROP UNTIL 4 DAYS IDLE

                         NO DELETE LOCKED

                         HASHING USING “SHA2_512” VERSION “v1”;

CREATE BLOCKCHAIN TABLE auditor.ledger_emp_n (employee_id NUMBER, employee_name varchar2(15), salary NUMBER)

*

ERROR at line 1:

ORA-05747: maximum retention time too high, should be less than or equal to 0