Oracle BLOCKCHAIN TABLES
Version 20c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Be sure to view the full listing of monographs in Morgan's Library
Purpose Blockchain tables are intended to provide an extremely tamper resistant means of storing relational data in a form wherein it can be accessed using SQL. Blockchain tables intended for use in an environment where it is required that the ledger be distributed to multiple organizations/locations, but clearly that distribution is possible if one considers integration with data distribution tools such as Data Guard and GoldenGate.
Data Dictionary Objects
ALL_SEGMENTS CDB_TAB_COLS DBA_TABLES
ALL_TAB_COLS CDB_TABLES USER_SEGMENTS
ALL_TABLES DBA_SEGMENTS USER_TAB_COLS
CDB_SEGMENTS DBA_TAB_COLS USER_TABLES
First Available 20c
System Privileges
ALTER ANY TABLE CREATE ANY TABLE DROP ANY TABLE
ALTER TABLE CREATE TABLE DROP TABLE
   
 
General (new 20c)
Block Change table with NO DELETE LOCKED CREATE BLOCKCHAIN TABLE <schema_name>.<table_name(
<column_name> <column_data_type>)
NO DROP [UNTIL <integer> DAYS IDLE]
NO DELETE LOCKED
HASHING USING "<hashing_algorithm>" VERSION "<version_number>"
[sharing_clause]
[memoptimize_clause]
[relational_properties];
CREATE BLOCKCHAIN TABLE ledger1(
tx_id INTEGER,
tx_date DATE,
tx_value NUMBER(10,2))
TABLESPACE uwdata
NO DROP UNTIL 31 DAYS IDLE
NO DELETE LOCKED
HASHING USING "SHA2_512" VERSION "v1";
Block Change table with NO DELETE UNTIL CREATE BLOCKCHAIN TABLE <schema_name>.<table_name(
<column_name> <column_data_type>)
NO DROP [UNTIL <integer> DAYS IDLE]
NO DELETE UNTIL <integer> DAYS AFTER INSERT [LOCKED]
HASHING USING "<hashing_algorithm>" VERSION "<version_number>"
[sharing_clause]
[memoptimize_clause]
[relational_properties];
CREATE BLOCKCHAIN TABLE ledger2(
tx_id INTEGER,
tx_date DATE,
tx_value NUMBER(10,2))
TABLESPACE uwdata
NO DROP UNTIL 31 DAYS IDLE
NO DELETE UNTIL 90 DAYS AFTER INSERT
HASHING USING "SHA2_512" VERSION "v1";
Block Chain table with range partitioning CREATE BLOCKCHAIN TABLE <schema_name>.<table_name(
<column_name> <column_data_type>)
NO DROP UNTIL
NO DELETE LOCKED
HASHING USING "<hashing_algorithm>" VERSION "<version_number>"
PARTITION BY ....;
CREATE BLOCKCHAIN TABLE ledger_partitioned(
tx_id     NUMBER,
tx_date   DATE,
grantor   VARCHAR2(60),
grantee   VARCHAR2(60))
NO DROP UNTIL 60 DAYS IDLE
NO DELETE UNTIL 31 DAYS AFTER INSERT
HASHING USING "SHA2_512" VERSION "v1"
PARTITION BY RANGE(trans_date) (
 PARTITION p1 VALUES LESS THAN (TO_DATE('01-01-2018','dd-mm-yyyy')),
 PARTITION p2 VALUES LESS THAN (TO_DATE('01-01-2019','dd-mm-yyyy')),
 PARTITION p3 VALUES LESS THAN (TO_DATE('01-01-2020','dd-mm-yyyy')),
 PARTITION p4 VALUES LESS THAN (TO_DATE('01-01-2021','dd-mm-yyyy')));
   
 
 
Block Change Tracking Table Demos
  conn / as sysdba

SQL> CREATE BLOCKCHAIN TABLE ledger (
  2  tx_id    INTEGER,
  3  tx_date  DATE,
  4  tx_value NUMBER(10,2))
  5  NO DROP UNTIL 31 DAYS IDLE
  6  NO DELETE LOCKED
  7  HASHING USING "SHA2_512" VERSION "v1";
CREATE BLOCKCHAIN TABLE ledger (txn_id INTEGER, tx_date DATE, tx_value NUMBER(10,2))
*
ERROR at line 1:
ORA-05729: blockchain table cannot be created in root container


SQL> CREATE BLOCKCHAIN TABLE ledger(
  2  tx_id INTEGER,
  3  tx_date DATE,
  4  tx_value NUMBER(10,2))
  5  NO DROP UNTIL 31 DAYS IDLE
  6  NO DELETE LOCKED
  7  HASHING USING "SHA2_512" VERSION "v1";

Table created.

SQL> desc ledger
Name                          Null?    Type
----------------------------- -------- --------------------
TX_ID                                  NUMBER(38)
TX_DATE                                DATE
TX_VALUE                               NUMBER(10,2)


SQL> SELECT column_id, column_name, data_type, hidden_column, virtual_column, user_generated
  2  FROM user_tab_cols
  3* ORDER BY user_generated, column_id;

COLUMN_ID  COLUMN_NAME               DATA_TYPE                    HID VIR USE
---------- ------------------------- ---------------------------- --- --- ---
           ORABCTAB_SIGNATURE$       RAW                          YES NO  NO
           ORABCTAB_SIGNATURE_ALG$   NUMBER                       YES NO  NO
           ORABCTAB_SIGNATURE_CERT$  RAW                          YES NO  NO
           ORABCTAB_SPARE$           RAW                          YES NO  NO
           ORABCTAB_USER_NUMBER$     NUMBER                       YES NO  NO
           ORABCTAB_CREATION_TIME$   TIMESTAMP(6) WITH TIME ZONE  YES NO  NO
           ORABCTAB_HASH$            RAW                          YES NO  NO
           ORABCTAB_INST_ID$         NUMBER                       YES NO  NO
           ORABCTAB_CHAIN_ID$        NUMBER                       YES NO  NO
           ORABCTAB_SEQ_NUM$         NUMBER                       YES NO  NO
        1  TX_ID                     NUMBER                       NO  NO  YES
        2  TX_DATE                   DATE                         NO  NO  YES
        3  TX_VALUE                  NUMBER                       NO  NO  YES


col ORABCTAB_CREATION_TIME$ format a40
col ORABCTAB_HASH$ format a130

SQL> SELECT orabctab_user_number$, orabctab_creation_time$, orabctab_inst_id$
  2  FROM ledger;

ORABCTAB_USER_NUMBER$ ORABCTAB_CREATION_TIME$                ORABCTAB_INST_ID$
--------------------- -------------------------------------- -----------------
                  111 18-NOV-20 06.54.20.685413 PM +00:00                    1


SQL> SELECT orabctab_chain_id$, orabctab_seq_num$
  2  FROM ledger;

ORABCTAB_CHAIN_ID$ ORABCTAB_SEQ_NUM$
------------------ -----------------
                16                 1


SQL> SELECT orabctab_hash$
  2  FROM ledger;

ORABCTAB_HASH$
--------------------------------------------------------------------------------------------------------------------------------
EDB342BB5A6D3DC4731AE30BEC65BEDA23EDE4655F73A6A0BA14B2BEBC580C7636AE400853181B934A15855EF3284E7A0D71C212368862A0000CE1DFF6514135


SQL> SELECT object_name, object_type
  2  FROM user_objects_ae;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------------
LEDGER                         TABLE


SQL> SELECT object_name, object_type
  2* FROM user_objects_ae;

OBJECT_NAME     OBJECT_TYPE
--------------- -------------------
LEDGER          TABLE


SQL> SELECT table_name, table_type
  2  FROM user_all_tables;

TABLE_NAME      TABLE_TYPE
--------------- -------------------
LEDGER


SQL> SELECT segment_name, segment_type
  2  FROM user_segments;

SEGMENT_NAME                   SEGMENT_TYPE
------------------------------ ------------------


SQL> insert into ledger values (1, SYSDATE, 100);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT segment_name, segment_type
  2  FROM user_segments;

SEGMENT_NAME                   SEGMENT_TYPE
------------------------------ ------------------
LEDGER                         TABLE


SQL> UPDATE ledger SET tx_value = 200;
UPDATE ledger SET tx_value = 200
*
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain table


SQL> DELETE FROM ledger;
DELETE FROM ledger
*
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain table


SQL> TRUNCATE TABLE ledger;
TRUNCATE TABLE ledger
*
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain table


x
   

Related Topics
Database Security
Built-in Functions
Built-in Packages
Object Privileges
Partitioning
System Privileges
Tables
What's New In 19c
What's New In 20c