Relational Databases

  In Oracle version 21c, new features have been introduced for transportable tablespace (TTS) export/import. This article explores these capabilities in detail. Improvement 1: Parallel Execution of TTS Export/Import In Oracle 19c, TTS export/import operations could not be executed in parallel, as demonstrated by the following: Connected to: Oracle Database 19c Enterprise Edition Release 19.0 .0 .0 .0 – Production ORA-39002: invalid operation ORA-39047: Jobs of type TRANSPORTABLE cannot use multiple execution streams. Oracle addressed this limitation in version 21c, enabling parallel execution for TTS export/import: [oracle@oLinux7 ~]$ expdp directory=drm dumpfile=TTS_DUMP%U.dmp transport_tablespaces=tbs1,tbs2 transport_full_check= y parallel= 2 Export: Release 21.0 . 0 . 0 . 0 – Production on Tue Oct 12 06 : 48 : 54 2021 Version 21.3 . 0 . 0 . 0 Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS Processing object type TRANSPORTABLE_EXPOR...

  Changes in a query’s execution plan can occur due to straightforward reasons such as adding or dropping indexes, table partitioning, or index partitioning. However, identifying the root cause of an optimizer behavior change is not always straightforward because sometimes changes in the Optimizer Environment itself cause a new execution plan to be generated. For example, in one session the parameter  OPTIMIZER_INDEX_COST_ADJ , which controls the optimizer’s inclination to use indexes, might be set to 1, while in another session it could be set to 1000! Undoubtedly, such differences in the Optimizer Environment can cause execution plans for some queries to change. This document explains how to detect whether a change in a query’s execution plan is caused by a change in the Optimizer Environment. More specifically, it shows how to determine which parameters and environmental factors triggered the new execution plan. We will use a new capability introduced by Oracle in version 2...

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