Blockchain and Immutable Tables Enhancements in Oracle Database 23ai

By -

 This article highlights several new features introduced in Oracle 23ai for Blockchain and Immutable tables.

Adding and Removing Columns in Blockchain and Immutable Tables

In Oracle 23ai, you can add columns to Blockchain tables (specifically version v2 of these tables):

SQL> CREATE BLOCKCHAIN TABLE blockchaintb1 (
  id    NUMBER(10),
  desc1 VARCHAR2(100)
)
NO DROP UNTIL 20 DAYS IDLE
NO DELETE UNTIL 20 DAYS AFTER INSERT
HASHING USING "SHA2_512" VERSION "v2";  
Table created.

SQL> ALTER TABLE blockchaintb1 ADD (desc2 VARCHAR2(1000));
Table altered.

Similarly, column removal is also supported in this version:

SQL> ALTER TABLE blockchaintb1 DROP COLUMN desc2;
Table altered.

Even after data is inserted into these tables, the above operations can be repeated:

SQL> INSERT INTO blockchaintb1 VALUES(1, 'Amir Hozhabri', 'Sori Masoud');
1 row created.

SQL> COMMIT;
Commit complete.

SQL> ALTER TABLE blockchaintb1 DROP COLUMN desc2;
Table altered.

Note: To check the version of each Blockchain table, you can query the user_blockchain_tables view using the column TABLE_VERSION.

A similar feature is available for version v2 of Immutable tables:

SQL> CREATE IMMUTABLE TABLE Immtb1 (
  id    NUMBER(10),
  desc1 VARCHAR2(100)
)
NO DROP UNTIL 200 DAYS IDLE
NO DELETE UNTIL 200 DAYS AFTER INSERT
VERSION "v2";
Table created.

SQL> ALTER TABLE Immtb1 ADD (desc2 VARCHAR2(1000));
Table altered.

SQL> ALTER TABLE Immtb1 DROP COLUMN desc2;
Table altered.

Configuring Maximum Idle Retention Time

Incorrect configuration of Idle Retention Time can cause challenges. Oracle 23ai introduces a new parameter, BLOCKCHAIN_TABLE_RETENTION_THRESHOLD, which enforces a ceiling for NO DROP UNTIL .. DAYS IDLE.

SQL> ALTER SYSTEM SET BLOCKCHAIN_TABLE_RETENTION_THRESHOLD=45;
System altered.

Without the TABLE RETENTION privilege, users cannot create tables exceeding this threshold:

SQL> CREATE BLOCKCHAIN TABLE blkchaintb1 (
  id    NUMBER(10),
  desc1 VARCHAR2(100)
)
NO DROP UNTIL 2000 DAYS IDLE
NO DELETE UNTIL 30 DAYS AFTER INSERT
HASHING USING "SHA2_512" VERSION "v1";
ORA-05807: Blockchain or immutable table "USEF"."BLKCHAINTB1" cannot have idle retention greater than 45 days.

SQL> CREATE BLOCKCHAIN TABLE blkchaintb1 (
  id    NUMBER(10),
  desc1 VARCHAR2(100)
)
NO DROP UNTIL 44 DAYS IDLE
NO DELETE UNTIL 30 DAYS AFTER INSERT
HASHING USING "SHA2_512" VERSION "v1";
Table created.

Granting the TABLE RETENTION privilege overrides this restriction:

SQL> GRANT TABLE RETENTION TO usef;
Grant succeeded.

SQL> CONNECT usef/a@OEL8:1521/TEST
Connected.

SQL> CREATE BLOCKCHAIN TABLE blkchaintb1 (
  id    NUMBER(10),
  desc1 VARCHAR2(100)
)
NO DROP UNTIL 2000 DAYS IDLE
NO DELETE UNTIL 30 DAYS AFTER INSERT
HASHING USING "SHA2_512" VERSION "v1";
Table created.

Blockchain Table Row Versions

As you know, records in Blockchain tables cannot be updated:

SQL> CREATE BLOCKCHAIN TABLE blkchaintb1 (
  id    NUMBER(10),
  desc1 VARCHAR2(100)
)
NO DROP UNTIL 2000 DAYS IDLE
NO DELETE UNTIL 30 DAYS AFTER INSERT
HASHING USING "SHA2_512" VERSION "v1";
Table created.

SQL> INSERT INTO blkchaintb1 VALUES(1, 'usefzadeh.co');
1 row created.

SQL> UPDATE blkchaintb1 SET desc1='usefzadeh.com' WHERE id=1;
ORA-05715: operation not allowed on the blockchain or immutable table

For corrections or updates, new records must be inserted. Identifying the latest version of a record becomes a challenge.

Oracle 23ai addresses this with the Row Versions feature. By including WITH ROW VERSION AND USER CHAIN in the CREATE TABLE statement, a view (<table_name>_LAST$) is generated to display the latest version of each record.

SQL> CREATE BLOCKCHAIN TABLE blkchaintb3 (
  id    NUMBER(10),
  desc1 VARCHAR2(100)
)
NO DROP UNTIL 2000 DAYS IDLE
NO DELETE UNTIL 30 DAYS AFTER INSERT
HASHING USING "SHA2_512"
WITH ROW VERSION AND USER CHAIN fruit_chain (id)
VERSION "v2";  
Table created.

SQL> INSERT INTO blkchaintb3 VALUES(1, 'usefzadeh.co');
1 row created.

SQL> INSERT INTO blkchaintb3 VALUES(1, 'usefzadeh.com');
1 row created.

SQL> INSERT INTO blkchaintb3 VALUES(2, 'test');
1 row created.

SQL> COMMIT;
Commit complete.

The view blkchaintb3_last$ displays the latest version of each id:

SQL> DESC blkchaintb3_last$;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(10)
 DESC1                                              VARCHAR2(100)

SQL> SELECT * FROM blkchaintb3_last$;
        ID DESC1
---------- ---------------
         1 usefzadeh.com
         2 test

Comments

Post a Comment

Popular posts from this blog

Oracle 23ai — Speed up IMPDP Using NOVALIDATE Constraints

By -
  When executing the   IMPDP   command, some operations do not support parallel execution. Creating constraints is one such operation. Oracle must validate the current data in the table before creating a constraint, which requires a full table scan for each validation. This process can be very time-consuming. Consider the following constraints: ALTER TABLE VAHID.MYTB ADD CONSTRAINT CHECK1 CHECK (col3 > 50 ); ALTER TABLE VAHID.MYTB ADD CONSTRAINT CHECK2 CHECK (col9 IN ( 5 , 15 )); We drop the  MYTB  table and recreate it using a dump file: SQL > drop table mytb; Table dropped. W -1 Processing object type TABLE_EXPORT / TABLE / TABLE_DATA W -1 . . imported "VAHID"."MYTB" 19 GB 337830912 rows in 83 seconds using external_table W -1 Processing object type TABLE_EXPORT / TABLE / CONSTRAINT / CONSTRAINT W -1 Completed 2 CONSTRAINT objects in 208 seconds As shown, creating the two constraints ta...
Read more

Staging Tables in Oracle 23ai

By -
  As you may know, using   staging tables   is a common practice in   Data Warehouse   environments, primarily during the data loading process into the   Warehouse Database . Typically, raw data received from   source databases   is first loaded into a temporary area, referred to as   staging tables . After cleansing inconsistent and invalid data and transforming it into a standard format, the cleaned data is stored in the final operational tables. In addition to initial data loading,   staging tables   are also used for updates and are often truncated before reuse. In   Oracle 23ai , a new minor feature has been introduced for this purpose, allowing users to create tables specifically designed as   staging tables . This is achieved by adding the   FOR STAGING   clause to the   CREATE TABLE   statement. Creating Staging Tables Using the   FOR STAGING   clause, staging tables can be created as ...
Read more

Oracle 23ai — Track Table and Partition Scan Access

By -
Image
  In Oracle 23ai, a new view named   [dba|all|user]_table_access_stats   has been introduced, enabling users to view the access history of tables and partitions. This view shows how many times each table or partition has been scanned in each instance and the last time a table was accessed. SQL > create table jadval(id number, name varchar2( 100 ), last_name varchar2( 100 )); Table created. SQL > insert into jadval values ( 1 , 'payan' , 'rafat' ); 1 row created. SQL > select TABLE_NAME, PARTITION_NAME, INSTANCE_ID, READ_COUNT, LAST_ACCESSED_TIME from user_table_access_stats; no rows selected SQL > select * from jadval; ID NAME LAST_NA ---------- ------- ------- 1 payan rafat SQL > select TABLE_NAME, PARTITION_NAME, INSTANCE_ID, READ_COUNT, LAST_ACCESSED_TIME from user_table_access_stats; TABLE_NAME PARTITION_NAME INSTANCE_ID READ_COUNT LAST_ACCESSED_TIME ---------- --------------- ----------- --------...
Read more