Blockchain table in 19c

on

Recently there is a lot of interest in blockchain technology most of it is the cause of Bitcoin and Ethereum. There is a lot of application and cryptocurrency based on Ethereum(ERC-20). Nowadays a lot of applications are build based on blockchain technology. The advantage of blockchain is that it is tamper-proof and every transaction is recorded and visible to everyone and still no one can modify it.

Oracle has been very active in automating and adding features to the Oracle Database to retain and gain a more customer base. Since it doesn't want their customer to leave just cause they wanted to implement blockchain in their application. Oracle made blockchain tables available in 21c by default. Not every customer upgrades their database often. What if the customer who is using 19c wanted to implement a blockchain table in their application, don't worry about it Oracle has added the blockchain table in patch 32431413.

Let us try to create the blockchain table in the 19c database without applying patch 32431413.

SQL> create blockchain table oracledbaarena (d date, amt number)
 no drop until 1 days idle
  no delete until 31 days after insert
  hashing using "sha2_512" version v1;  2    3    4
create blockchain table oracledbaarena (d date, amt number)

       *
ERROR at line 1:
ORA-00901: invalid CREATE command

To apply patch 32431413, your database version should be  19.10.0.0.
My database version is 19.9.0.0 so I need to upgrade my database to 19.10.0.0 to apply the patch 32431413  and try testing. 

Instead of upgrading the database, I have created a 21c database in my oracle trial account to test my theories of blockchain tables in the oracle database.

No matter what condition you set while creating the table if no row has been inserted then the table can be deleted.
SQL> create blockchain table oracledbaarena (d date, amt number)
 no drop until 1 days idle
  no delete until 31 days after insert
  hashing using "sha2_512" version v1;  2    3    4

Table created.

SQL> drop table oracledbaarena;

Table dropped.

Once you insert the data in the table only option is to wait "until the number of days idle" is set when creating the table or if the database is not used by many users and if you can get the database bounce.
What you can do is change the server time and drop the table. Be careful while changing the server time my OCI server got rebooted after I changed the time.


SQL> create blockchain table oracledbaarena (d date, amt number)
 no drop until 1 days idle
  no delete until 31 days after insert
  hashing using "sha2_512" version v1;  2    3    4

Table created.

SQL> insert into oracledbaarena values (sysdate,1);

1 row created.

SQL>  drop table oracledbaarena;
 drop table oracledbaarena
            *
ERROR at line 1:
ORA-05723: drop blockchain table ORACLEDBAARENA not allowed


SQL> select sysdate from dual;

SYSDATE
---------
09-FEB-21

SQL> ALTER SYSTEM SET FIXED_DATE='2021-02-10';

System altered.

SQL>  select sysdate from dual;

SYSDATE
---------
10-FEB-21

SQL> drop table oracledbaarena;
drop table oracledbaarena
           *
ERROR at line 1:
ORA-05723: drop blockchain table ORACLEDBAARENA not allowed

SQL> exit
Disconnected from Oracle Database 21c Standard Edition 2 Release 21.0.0.0.0 - Production
Version 21.1.0.0.0

[opc@oracledbaarena21c ~]$ sudo su - root
Last login: Tue Feb  9 17:12:03 UTC 2021
[root@oracledbaarena21c ~]# date +%Y%m%d -s "20210211"
20210211

[oracle@oracledbaarena21c ~]$ sqlplus / as sysdba
SQL> select sysdate from dual;

SYSDATE
---------
11-FEB-21
SQL> drop table oracledbaarena;

Table dropped.
While doing this research found something strange. Let us discuss it in the next post.

Comments

Post a Comment