Using Blockchain tables in Oracle Database 19c by examples

Blockchain tables in Oracle 19c
Andrew Vasenin

Andrew Vasenin

The author. Sphere of interests: IT specialist (programming, administration, DBA). PhD in Economics. More details.

To be able to use the Blockchain table in the Oracle 19c release, you will need to apply the latest patches and change the compatibility setting (which is unusual). The blockchain feature was first introduced in the Oracle 21c release and ported to 19c with the 19.10 RU patch in the January 2021 CPU. So, let's explore this possibility using the example of a patched Oracle database up to version 19.11 (CPU April 2021).

Step 1. To raise the compatibility parameter, disable the database:

SQL> shutdown immediate;

Step 2. Next, let's edit the init.ora file and change the compatibility parameter value:

compatible=’19.11.0′

Step 3. Then we start the database again:

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

Step 4. Check if the compatibility setting was actually applied:

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

...
compatible
19.11.0
Database will be completely compatible with this software version

Step 5. Create a Blockchain table. In the pluggable database PDBS1, we create a test tablespace and a user, which we grant the necessary privileges:

CREATE TABLESPACE ledgertesttbs;

CREATE USER auditor identified by audit_pass DEFAULT TABLESPACE ledgertesttbs;

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

GRANT execute ON sys.dbms_blockchain_table TO auditor;

Step 6. Now, logging into the PDBS1 database using the auditor account, we will create a blockchain table named AUDITOR.LEDGER_EMP, which will maintain a tamper-proof ledger of current and historical transactions in PDBS1. Rows can never be deleted in the AUDITOR.LEDGER_EMP blockchain table. This blockchain table can be deleted only after the expiration of 3 days, provided that there is no manipulation with it.

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”;

Let's check the existing blockchain tables by querying user_blockchain_tables:

SQL> SELECT *  FROM   user_blockchain_tables;

request to user_blockchain_tables:

Step 7. Now let's insert 1 record into the ledger_emp table:

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

SQL> COMMIT;

Step 8. Now, if we try to delete this table, we get an error message:

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

If we try to delete all records from the table, we get the following error:

SQL> delete from auditor.ledger_emp;

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

Step 9. Now let's check the integrity of the hash values of the string:

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;

check the integrity of the hash values of the string

Step 10. Oracle 19C has a parameter called BLOCKCHAIN_TABLE_MAX_NO_DROP that determines the amount of time a table can be inactive before it can be dropped. This parameter can be very useful if you want to protect a table from accidental deletion by setting the nodrop parameter to a very high value.

Try to execute from PDB level:

sqlplus / as sysdba

SQL> alter session set container=PDBS1;

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

Try from CDB level:

sqlplus / as sysdba

SQL> alter system set blockchain_table_max_no_drop=0;

System altered.

Even though the documentation states that you can change it from the PDB level ?! (apparently this is a documentation error):

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 try to create a blockchain table with a number of days greater than zero:

SQL> CREATE BLOCKCHAIN TABLE auditor.ledger_emp1 (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_emp1 (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

I hope that the given examples provided a visual representation of the possibilities of working with Blockchain tables in Oracle Database 19c.

Вас заинтересует / Intresting for you:

Why Learn More About Oracle SQ...
Why Learn More About Oracle SQ... 690 views Виктор Thu, 16 Jul 2020, 17:59:54
Oracle RAC Solution short revi...
Oracle RAC Solution short revi... 860 views Боба Sun, 21 Jun 2020, 12:06:33
RMAN: Backing Up Tablespaces
RMAN: Backing Up Tablespaces 94 views Андрей Волков Thu, 23 Sep 2021, 16:53:02
RMAN: Excluding   Oracle Table...
RMAN: Excluding Oracle Table... 63 views Андрей Волков Wed, 29 Sep 2021, 18:27:09