Platform, Transaction, and Server Configuration

Changes to Release Platforms

HP, AIX, and PPC64 have been removed from the supported release platforms. The current release platform table in the manual lists linux-x86_64 for servers and Linux- and Windows-based client platforms. The appropriate package must be selected for the target installation environment.

For more information, refer to Release Platform.

SERIALIZABLE Isolation Level No Longer Supported in Cluster Systems

The SERIALIZABLE transaction isolation level is no longer supported in cluster systems. In a cluster environment, READ COMMITTED must be used when setting the isolation level with SET TRANSACTION or SET SESSION CHARACTERISTICS AS TRANSACTION.
The isolation level set using SET TRANSACTION applies to the next transaction and is restored to the default setting when that transaction ends.

For more information, refer to the isolation level sections in SET TRANSACTION and SET SESSION CHARACTERISTICS.

Default Value of DEFAULT_INDEX_PCTFREE

The default value of DEFAULT_INDEX_PCTFREE has been changed to 10. When PCTFREE is not explicitly specified during index creation, the value of this property is applied. Therefore, the updated default value of 10 also applies when PCTFREE is omitted from existing creation scripts.

For more information, refer to DEFAULT_INDEX_PCTFREE.

Default Value of DEFAULT_MAXTRANS

The default value of DEFAULT_MAXTRANS has been changed to 32. When MAXTRANS is not explicitly specified during table or index creation, the value of this property is applied.

For more information, refer to DEFAULT_MAXTRANS.

Name and Default Value of BUFFER_DIRTY_PAGE_LIMIT

INCREMENTAL_CHECKPOINT_CRITERIA has been renamed to BUFFER_DIRTY_PAGE_LIMIT, and its default value has been changed to 0. The previous name is documented as an alias in the manual.

This property determines when to perform an incremental checkpoint based on the number of modified pages in the buffer cache. The default value of 0 represents infinity, meaning that an incremental checkpoint is not triggered by this criterion, even if all pages in the buffer cache have been modified.

For more information, refer to BUFFER_DIRTY_PAGE_LIMIT.

Changes to Cluster Communication Buffer Properties

CLUSTER_CM_BUFFER_COUNT has been deprecated, and the following properties have been added to configure the number of communication buffers for each dispatcher.

The default value for each property is 1. These properties cannot be modified using ALTER SESSION or ALTER SYSTEM.

For more information, refer to LOCKABLE_DISPATCHER_CM_BUFFER_COUNT, LOCKLESS_DISPATCHER_CM_BUFFER_COUNT, SYNC_DISPATCHER_CM_BUFFER_COUNT.

Consolidation of Plan Cache Control Properties

MAXIMUM_FLANGE_COUNT has been deprecated, and the plan cache is now controlled solely by PLAN_CACHE_SIZE. PLAN_CACHE_SIZE specifies the amount of memory allocated to the plan cache in bytes. This value must not be interpreted as the number of SQL statements.

For more information, refer to PLAN_CACHE_SIZE.

Online Modification of PLAN_CACHE_SIZE

PLAN_CACHE_SIZE can now be modified online without restarting the server. This property specifies the amount of memory allocated to the SQL execution plan cache. Session-level modifications are not allowed; only system-level modifications are permitted.

For more information, refer to PLAN_CACHE_SIZE.

Removal of REF_COUNT from V$SQL_CACHE

The REF_COUNT column has been removed from V$SQL_CACHE. This view provides statistics on shared SQL execution plans, including SQL text, the number of plans, plan size, and plan text. Monitoring queries that explicitly reference REF_COUNT must be updated to reflect the revised column list.

For more information, refer to V$SQL_CACHE.

Default Value of AGING_PLAN_INTERVAL

The default value of AGING_PLAN_INTERVAL has changed from 0 to 3. The unit is seconds. SQL execution plans older than the specified interval become eligible for aging.

For more information, refer to AGING_PLAN_INTERVAL.

Default Value of SESSION_MEMORY_INIT_SIZE

The default value of SESSION_MEMORY_INIT_SIZE has changed from 131072 (128 KB) to 524288 (512 KB). This property specifies the initial memory size allocated for a dedicated session.

For more information, refer to SESSION_MEMORY_INIT_SIZE.

Relaxed TRANSACTION_TABLE_SIZE Constraints in Cluster Systems

The requirement for all cluster members to have the same TRANSACTION_TABLE_SIZE value has been removed. The database must be restarted to change the transaction table size of an individual member.

When reducing the configured value, it must be set to a value greater than the maximum slot identifier used by prepared transactions after restart recovery. Setting it to a value less than or equal to this maximum may cause the restart to fail.

For more information, refer to TRANSACTION_TABLE_SIZE.

Changes to Cluster Deadlock Detection

With the introduction of built-in cluster deadlock detection, the existing CLUSTER_DEADLOCK_TIMEOUT property has been deprecated.

Changes to Cluster Asynchronous Replica Configuration

Cluster replica updates have been improved so that async replica is used by default. If asynchronous replication is not supported in a given environment, the system handles this automatically. The existing CLUSTER_ASYNC_REPLICATION property has been deprecated.

Default Value of SHARED_MEMORY_STATIC_SIZE

The default value of the SHARED_MEMORY_STATIC_SIZE property has changed to 800M (838,860,800 bytes). This property specifies the size of the Shared Static Area (SSA) and cannot be modified using the ALTER SESSION or ALTER SYSTEM statements.

For more information, refer to SHARED_MEMORY_STATIC_SIZE.

Rename of INST_TABLE_PAGE_SIZE

The INST_TABLE_BLOCK_SIZE property has been renamed to INST_TABLE_PAGE_SIZE. The previous name remains supported as an alias. This property determines the page size of instant tables. If the fixed portion of an instant record exceeds the specified page size, a maximum record length error may occur.

For more information, refer to INST_TABLE_PAGE_SIZE.

Separation of Cluster Commit Communication

The cluster commit protocol has been separated to use a dedicated dispatcher and queue. As a result, the existing CLUSTER_COMMIT_STREAM_ISOLATION property has been deprecated..

The operational status can be monitored through the V$CLUSTER_DISPATCHER view, which provides dispatcher types and send/receive statistics. The TYPE column includes a COMMIT entry. The list of cluster queues and their queued item counts can be viewed in V$CLUSTER_QUEUE.

For more information, refer to V$CLUSTER_DISPATCHER, V$CLUSTER_QUEUE.

Changes to Control File Temporary File Configuration

The control file storage mechanism has been changed so that temporary files are no longer used. As a result, the CONTROL_FILE_TEMP_NAME property has been deprecated. This property was previously used to specify the temporary directory and file name for storing a copy of the control file.

For more information, refer to CONTROL_FILE_TEMP_NAME.

Renaming Online DDL Properties

The names of properties related to online DDL have been changed as follows. The previous property names remain supported as aliases for backward compatibility.

For more information, refer to ONLINE_DDL_BLOCK_READ_COUNT, ONLINE_DDL_SCAN_PARTITION, ONLINE_DDL_MAXIMUM_JOURNAL_REPLAY_COUNT, ONLINE_DDL_JOURNAL_REPLAY_THRESHOLD.

SQL and PSM

Restriction on Concurrent Execution of ALTER INDEX COALESCE

ALTER INDEX COALESCE can no longer be executed concurrently with other ALTER statements. If administrative tasks were previously executed concurrently in version 22c.1, their execution order must be adjusted.

COALESCE reclaims space by merging adjacent index leaf pages. Concurrent execution with DML operations continues to be supported as before. This restriction applies only to concurrent execution with other ALTER statements.

For more information, refer to ALTER INDEX name COALESCE.

Removal of the ADD CONSTRAINT ON SCHEMA Privilege

The ADD CONSTRAINT ON SCHEMA privilege has been removed. Table owners can add constraints using the ALTER ON TABLE privilege, which is automatically granted when CREATE TABLE is executed. Non-owners must have either the ALTER ON TABLE privilege on the target table or the ALTER TABLE ON SCHEMA privilege.

The owner of an added constraint is the table owner.

For more information, refer to ALTER TABLE name ADD CONSTRAINT.

Default Schema Path Change for New Users

PUBLIC has been excluded from the default user schema path applied when a user is created using the CREATE USER statement. The default search path now includes only the user's own schema. To use the same search path as in version 22c.1 and earlier, PUBLIC must be explicitly specified after the user's schema.

ALTER USER u1 SCHEMA PATH (u1, public);

Object references that omit the schema name are resolved based on the schema path. However, the default schema path for users created with the WITHOUT SCHEMA option is set to PUBLIC.

For more information, refer to Specifying a Schema in CREATE USER and Changing the Schema Path.

Renaming the Shard Divisor Option in Online DDL

The shard divisor option used in online DDL has been renamed to scan partition. Existing DDL scripts must be updated to use the current syntax, SCAN PARTITION integer. This option specifies the number of partitions into which shards are divided for relocation and synchronization. If omitted, the value of ONLINE_DDL_SCAN_PARTITION is used.
The DDL statements affected are as follows.

• ALTER DATABASE MOVE SHARD

• ALTER DATABASE REBALANCE

• ALTER DATABASE REBALANCE EXCLUDE CLUSTER GROUP

• ALTER DATABASE SYNCHRONIZE

• ALTER TABLE name MOVE SHARD

• ALTER TABLE name REBALANCE

• ALTER TABLE name REBALANCE EXCLUDE CLUSTER GROUP cluster_group_list

• ALTER TABLE name SYNCHRONIZE

Removal of MINSIZE Support in STORAGE

MINSIZE is no longer supported in the STORAGE clause of the following DDL statements. When upgrading existing DDL scripts, this option must be removed.
The supported segment attributes for tables are INITIAL, NEXT, and MAXSIZE, while those for indexes are INITIAL and NEXT.
The affected DDL statements are as follows.

• CREATE TABLE

• CREATE TABLE AS SELECT

• CREATE INDEX

• ALTER INDEX name STORAGE

• ALTER INDEX name REBUILD

• ALTER TABLE name ADD GLOBAL SECONDARY INDEX

• ALTER TABLE name ALTER GLOBAL SECONDARY INDEX

• ALTER TABLE name ALTER GLOBAL SECONDARY INDEX REBUILD

Removal of MAXSIZE Support in Index STORAGE

MAXSIZE is no longer supported in the STORAGE clause of DDL statements. Accordingly, this option must be removed from existing scripts that create, modify, or rebuild indexes and global secondary indexes.
The supported segment attributes are INITIAL and NEXT.
The affected DDL statements are as follows.

• CREATE INDEX

• ALTER INDEX name STORAGE

• ALTER INDEX name REBUILD

• ALTER TABLE name ADD GLOBAL SECONDARY INDEX

• ALTER TABLE name ALTER GLOBAL SECONDARY INDEX

• ALTER TABLE name ALTER GLOBAL SECONDARY INDEX REBUILD

Restriction on Concurrent Execution of GSI COALESCE

The ALTER TABLE ALTER GLOBAL SECONDARY INDEX COALESCE statement can no longer be executed concurrently with other ALTER statements. Accordingly, if administrative tasks were previously executed concurrently in version 22c.1, their execution order must be adjusted.

This statement performs a COALESCE operation on the global secondary index of the specified table to eliminate index fragmentation. The target table name must be specified when executing this operation. The complete syntax is ALTER TABLE name ALTER GLOBAL SECONDARY INDEX COALESCE.

For more information, refer to ALTER TABLE name ALTER GLOBAL SECONDARY INDEX COALESCE.

Changes to Built-in Package Installation

The installation method for some built-in packages provided in version 22c.1 and earlier has changed. SQL scripts are now provided so that users can install the required packages manually.
To continue using packages from previous versions, the SQL scripts for the required packages must be installed.
Each installation file is located in $GOLDILOCKS_HOME/admin/packages/. Connect to gsql with SYSDBA privileges and execute the script using the --import option.

For more information, refer to Installing DBMS_LOCK, Installing DBMS_OUTPUT, Installing DBMS_SQL, and Installing DBMS_STANDARD.

API and Development Environment

Renaming the ODBC ALTERNATE_LOCATOR Connection Attribute

The ODBC connection attribute has been renamed from ALTERNATE_LOCATORS to ALTERNATE_LOCATOR. This attribute specifies a locator that provides alternative connection information when the glocator does not respond.
If the existing configuration is used, the attribute name in the locator DSN section of the odbc.ini file must be changed to the following format: ALTERNATE_LOCATOR=HOST=address:PORT=port

For more information, refer to ODBC: locator DSN Keyword.

Separation of Field Roles in SQL_LONG_VARIABLE_LENGTH_STRUCT for ODBC

The field layout of SQL_LONG_VARIABLE_LENGTH_STRUCT, which is used with non-standard ODBC data types, has been changed. buf_len specifies the capacity of the arr buffer, while len specifies the actual data length. Both values are expressed in bytes.
This structure is used with SQL_C_LONGVARCHAR and SQL_C_LONGVARBINARY. The application must allocate a buffer, set arr and buf_len, populate the buffer with input data, and then set len to the actual data length before binding.

For more information, refer to ODBC: SQL_C_LONGVARCHAR Binding Example.

Renaming the JDBC ALTERNATE_LOCATOR Connection Property

The JDBC connection property ALTERNATE_LOCATORS has been renamed to ALTERNATE_LOCATOR. When configuring JDBC properties, the lowercase form alternate_locator must be used, and the address of the alternative glocator must be specified in IP:PORT format.
For example, the property can be set in the Properties object passed when establishing a connection as follows: prop.setProperty("alternate_locator", "127.0.0.1:42582");

For more information, refer to JDBC: Example of Setting the Locator Property.

Renaming the Embedded SQL GET CLUSTER_GROUP_ID Statement

The Embedded SQL statement GET GROUPID has been renamed to GET CLUSTER_GROUP_ID. In a cluster environment that uses a global connection, the group ID of a target SQL statement can be obtained by specifying the target SQL statement after EXEC SQL GET CLUSTER_GROUP_ID INTO :group_id.
The target SQL statement must be a DELETE, INSERT, SELECT, or UPDATE statement on a table with a shard key defined. The :group_id variable must use a signed numeric type.
The target SQL statement is not actually executed; instead, it is cached in a prepared state. An invalid ID value of -1 may be returned.

For more information, refer to Embedded SQL: EXEC SQL GET CLUSTER_GROUP_ID INTO.

Separation of Field Roles in the Embedded SQL LONG VARCHAR Structure

The field layout of SQL_LONG_VARIABLE_LENGTH_STRUCT, which represents LONG VARCHAR data in Embedded SQL, has changed. The buf_len field specifies the capacity of the arr buffer, while the len field specifies the actual data length. Both values are measured in bytes.
The application must allocate memory for arr and set buf_len, then set len to the actual length of the data. The application is responsible for freeing the allocated memory after use.

For more information, refer to Embedded SQL: LONG VARCHAR.

Changes to Negative Length Error Handling for Embedded SQL VARCHAR and VARBINARY

Changes to Negative Length Error Handling for Embedded SQL VARCHAR and VARBINARY
When an Embedded SQL VARCHAR or VARBINARY variable is used as an IN or IN_OUT parameter, an error is now raised if its len value is negative. Therefore, data must be stored in arr before input, and len must be set to the exact length of the actual data.
In the manual examples, strlen(varstr.arr) is used to set the length of string data, while the number of bytes copied is assigned to len for binary data.

For more information, refer to Embedded SQL: VARCHAR Constraints and VARBINARY Constraints.

Changes to PyDBC Python 3 Build and Packaging

The PyDBC Python 3 package now uses a pyproject.toml-based build system and supports wheel and sdist packaging.
PyDBC is a C extension module that uses the GOLDILOCKS ODBC library, gdlcs. On Linux, the package can be installed by running python3 -m pip install . in the $GOLDILOCKS_HOME/app_dev/pygoldilocks/ver3 directory. The major and minor versions of Python used for installation must match those used to run PyDBC.

For more information, refer to PyDBC: Python 3 Installation.

Clarification of the Unsupported Value of cursor.lastrowid in PyDBC

The cursor.lastrowid attribute in PyDBC is not supported and always returns None. Therefore, the identifier of an inserted row cannot be retrieved using this attribute.

For more information, refer to PyDBC: Cursor.

Changes to Time Zone Type Return Behavior in PyDBC Python 3

In PyDBC Python 3, the TIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE data types are now returned as timezone-aware objects that preserve the UTC offset. The corresponding Python types are datetime.time and datetime.datetime, respectively.

For more information, refer to PyDBC: SQL Value Conversion in Python 3.

Time Zone Types Returned as Strings in PyDBC Python 2

In PyDBC Python 2, the TIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE data types are returned as strings. This differs from Python 3, which returns these types as datetime.time and datetime.datetime objects, respectively. Therefore, code that supports both Python versions must handle the return types differently.

For more information, refer to PyDBC: SQL Value Conversion in Python 2.

Improved Fractional Second Conversion for PyDBC TIME and TIMESTAMP Types

PyDBC has improved the handling of fractional second and microsecond conversions for the TIME and TIMESTAMP data types. These SQL types are returned as datetime.time and datetime.datetime objects in Python, respectively. The fractional second precision for both SQL types ranges from 0 to 6 digits, with a default precision of 6 digits.

For more information, refer to PyDBC: SQL Value Conversion, TIME Precision, and TIMESTAMP Precision.

Improved Decimal Precision and Scale Handling and Restrictions on Non-Finite Values in PyDBC

PyDBC has improved the handling of precision and scale for the NUMBER, NUMERIC, and DECIMAL data types. Decimal values, such as NaN and Infinity, are not supported. PyDBC passes Python decimal input values to ODBC as SQL_NUMERIC structures and returns retrieved SQL_NUMERIC values as decimal.Decimal objects.

For more information, refer to PyDBC: Parameter Passing and SQL Value Conversion.

SQLAlchemy 1.4 and 2.0 Support

SQLAlchemy versions 1.4 and 2.0 are supported. To use the GOLDILOCKS dialect, Python 3.8 or later, alembic, and the pygoldilocks package are required. After installing the dialect, connect by passing a URL in the format goldilocks://username:password@host:port to the create_engine() function.

For more information, refer to SQLAlchemy: Requirements.

Hibernate 6, 7, and 8 Support

Hibernate versions 6, 7, and 8 are supported.

For more information, refer to Hibernate: Interworking with GoldilocksDialect Class.

Utility

Deprecation of the directio-size Option in gloader/gloadernet

The --directio-size option has been deprecated. Check whether this option is used in existing execution commands and scripts.

redo_log File Type Name in gdump

The log file type name in gdump has been changed to redo_log. The file type is specified before the path of the file to be dumped. Use redo_log to output online or archived redo log files in text format.

For more information, refer to redo_log File Type Name in gdump.

datafile File Type Name in gdump

The data file type name in gdump has been changed to datafile. When dumping a data file, specify it in the format gdump datafile file path. The existing options are still available: --header outputs header information, while --number and --fetch specify the starting page number and the number of pages to output, respectively.

For more information, refer to datafile File Type Name in gdump.

ALTERNATE_LOCATOR Configuration Property Name in glocator

The glocator configuration property name has been changed from ALTERNATE_LOCATORS to ALTERNATE_LOCATOR. Specify the name of the peer locator used for replication, and define HOST and PORT in the corresponding name section of the same configuration file.

For more information, refer to ALTERNATE_LOCATOR Configuration Property Name in glocator.

glocator Replication Configuration

The glocator configuration has changed from a multi-instance configuration to a replication configuration. The two glocator instances are classified as master and sub (substitute) according to their startup order. Use the --sync option to connect the sub instance, which starts later, to the master instance, which starts first. During synchronization, the data from both glocator instances is merged. If duplicate entries have different values, the most recently updated value overwrites the other.

For more information, refer to glocator Replication Configuration.

Removal of the Argument from the glocator sync Option

The argument previously specified for the --sync option in glocator has been removed. glocator is now started using the glocator --start --sync command. The ALTERNATE_LOCATOR property must be configured in the configuration file in advance.

For more information, refer to Removal of the Argument from the glocator sync Option.

ALTERNATE_LOCATOR Configuration Property Name in gagent

The gagent configuration property name has been changed from ALTERNATE_LOCATORS to ALTERNATE_LOCATOR. Specify the name of the alternate locator in the [AGENT] section, and define HOST and PORT in the corresponding name section.

For more information, refer to ALTERNATE_LOCATOR Configuration Property Name in gagent.