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