Brief Overview of Oracle Block Chain Table
by Ravi Garkoti, Oracle Applications Database Administrator, Rackspace Technology
Blockchain
A blockchain is a tabulated series of records, which are also called blocks, that are linked together. The contents of each block comprise a cryptographic hash of the previous block, a timestamp, as well as transaction data.
As each block contains information about the immediate block before him, a chain is formed. Hence, blockchains are resistant to changes in their data because once recorded, any change in a block will change all blocks after it.
Cryptographic hash function
A cryptographic hash function is a mathematical algorithm that takes input data of an arbitrary size and converts it into output of a fixed size.
A hash function is expected to have the following features :
- The same input will give the same output always
- No to same inputs have same output
- A small change in input will change output drastically
Some popular hash functions are
Blockchain tables in Oracle
Oracle Blockchain tables are add-only tables in which only insert operations are allowed. Deleting rows is either prohibited or restricted based on time which is pre-defined. A hash function is part of row metadata and helps in making it tamper-proof. Blockchain Tables will be available with Oracle Database 21c and will also become available in the 19c database when the 19.10 release is available.
- This feature is a component of the Oracle Database so a new setup will be required.
- Indexing and partitioning are allowed in the blockchain table.
- Blockchain tables can be used along with (regular) tables in transactions and queries.
Blockchain tables are used to implement centralized blockchain applications where the central authority is the Oracle Database. It provides organizations with more customization and control to decide who can work on the chain. Participants must have privilege to insert data into the blockchain table. Blockchain content is defined and managed by the application. Centralized blockchains are provide higher throughput and lower latency in transactions
Rows in a blockchain table are corruption-free. Each row has a hash value which is generated using row data and has value of previous row. If any row is changed it will cause all subsequent chains to change since hash values will be updated
Example of Blockchain tables
Create the blockchain table
CREATE BLOCKCHAIN TABLE command requires additional conditions to be stated. The NO DROP, NO DELETE, HASHING USING, and VERSION clauses are mandatory.
CREATE BLOCKCHAIN TABLE emp (employee_id NUMBER, department_id NUMBER) NO DROP UNTIL 90 DAYS IDLE NO DELETE LOCKED HASHING USING "SHA2_512" VERSION "v1";
We can use user_blockchain_tables view to check the attributes of the blockchain table
SELECT row_retention, row_retention_locked, table_inactivity_retention, hash_algorithm FROM user_blockchain_tables WHERE table_name='EMP';
ROW_RETENTION ROW TABLE_INACTIVITY_RETENTION HASH_ALG
------------- --- -------------------------- --------
YES 90 SHA2_512
Description of the table.
The standard describe command will show only visible columns. USER_TAB_COLS view displays all internal column used to store internal information.
SELECT internal_column_id "Col ID", SUBSTR(column_name,1,30) "Column Name", SUBSTR(data_type,1,30) "Data Type", data_length "Data Length" FROM user_tab_cols WHERE table_name = 'LEDGER_EMP' ORDER BY internal_column_id;
Col ID Column Name Data Type Data Length
---------- ------------------------ ---------------------------- -----
1 EMPLOYEE_ID NUMBER 22
2 Department_ID NUMBER 22
3 ORABCTAB_INST_ID$ NUMBER 22
Insert row into the blockchain table.
INSERT INTO emp VALUES (106,12000);
Display the internal values of the row of the chain.
SELECT ORABCTAB_CHAIN_ID$, ORABCTAB_SEQ_NUM$, ORABCTAB_CREATION_TIME$,ORABCTAB_USER_NUMBER$, ORABCTAB_HASH$ FROM emp
Delete, Drop and Truncate
You can delete rows in a blockchain table only by using the DBMS_BLOCKCHAIN_TABLE package, and only rows that are outside the retention period. The standard delete command will not work for the blockchain table.
DBMS_BLOCKCHAIN_TABLE.DELETE_EXPIRED_ROWS(
schema_name IN VARCHAR2,
table_name IN VARCHAR2,
before_timestamp IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
number_of_rows_deleted OUT NUMBER);
If you created the table with the NO DELETE LOCKED attribute then you can never subsequently modify the row retention period for the purpose of deletion.
You can only increase the retention value of block chain table and the drop command will work only when the retention period is over.
Truncate command is not allowed for blockchain table.
Recent Posts
Escalando el acantilado de la ingeniería de plataformas: Un viaje hacia la innovación y la eficiencia
Julio 11th, 2024
Cómo funcionan las estrategias de fragmentación: Párrafo, oración y técnicas inteligentes
Julio 4th, 2024
Arquitectura basada en células en AWS
Mayo 6th, 2024
Una guía esencial para liberar el poder de la AI generativa
Julio 6th, 2023