Database Administration and Information Views

Parallel Recovery

Parallel recovery has been added. The number of slave threads participating in recovery can be specified using PARALLEL in ALTER DATABASE RECOVER. This feature can be used for database, data file, and tablespace recovery, as well as incomplete recovery. Data files are recovered by applying online and archived logs.

The number of slave threads can be set to a value between 0 and 64. If 0 or NOPARALLEL is specified, only the master thread is used. If the parallel recovery clause or the number of threads in PARALLEL is omitted, the value of RECOVERY_SLAVES is used. Complete recovery of the entire database must be performed in MOUNT phase and ARCHIVELOG mode.

For more information, refer to ALTER DATABASE RECOVER, recovery slaves clause.

Parallel Backup

Parallel backup has been added. The number of backup threads can be specified using the PARALLEL clause in ALTER DATABASE BACKUP INCREMENTAL. The allowed range is 1 to 64. If the number of threads in PARALLEL is omitted, it defaults to 1. If the PARALLEL clause itself is omitted, the operation runs in NOPARALLEL mode.

The actual number of threads used for parallel processing is limited by the number of target data files and PIECEs. If either the number of data files or the number of PIECEs is less than the specified number of parallel threads, parallel processing is performed using the smaller number.

For more information, refer to ALTER DATABASE BACKUP, <parallel clause>.

Parallel Restore

Parallel restore has been added. The PARALLEL clause can be used in ALTER DATABASE RESTORE and tablespace restore statements to specify the number of threads and restore data files from incremental backups. A full database restore must be performed in MOUNT phase.

The number of parallel threads can be set to a value between 1 and 64. If the number of threads in PARALLEL is omitted, it defaults to 1. If the PARALLEL clause itself is omitted, the operation runs in NOPARALLEL mode..

For more information, refer to ALTER DATABASE RESTORE, parallel clause.

DICTIONARY_SCHEMA Role Information Views

The following DICTIONARY_SCHEMA views have been added to provide information about roles and the privileges granted to them. These views allow users to check the list of roles, role grants to users and other roles, privileges granted to roles, and the active roles in the current session.

The ROLE_*_PRIVS views provide information about privileges granted to roles that the user can access.

For more information, refer to DBA_ROLES, DBA_ROLE_PRIVS, ROLE_COL_PRIVS, SESSION_ROLES.

DICTIONARY_SCHEMA Histogram Views

The following DICTIONARY_SCHEMA views have been added to provide information about histogram buckets. The BALANCE views retrieve bucket information for height-balanced histograms, while the FREQUENCY views retrieve bucket information for frequency histograms.

For more information, refer to ALL_HISTOGRAM_BALANCE, ALL_HISTOGRAM_FREQUENCY, DBA_HISTOGRAM_BALANCE, USER_HISTOGRAM_BALANCE.

DICTIONARY_SCHEMA Library Views

The following DICTIONARY_SCHEMA views have been added to allow users to query library objects and their privileges. These views help users identify the scope of library objects and distinguish between privileges granted and privileges received.

For more information, refer to ALL_LIBRARIES, ALL_LIBRARY_PRIVS, USER_LIBRARY_PRIVS, ROLE_LIBRARY_PRIVS를 참조한다.

DICTIONARY_SCHEMA Trigger Views

ALL_TRIGGERS, DBA_TRIGGERS, and USER_TRIGGERS have been added to query trigger objects. ALL_TRIGGERS displays triggers on tables that the current user can access, DBA_TRIGGERS displays all triggers in the database, and USER_TRIGGERS displays triggers owned by the current user.

For more information, refer to ALL_TRIGGERS, DBA_TRIGGERS, USER_TRIGGERS.

INFORMATION_SCHEMA Role Information Views

The following INFORMATION_SCHEMA views have been added to query roles and the privileges of enabled roles.

For more information, refer to ADMINISTRABLE_ROLE_AUTHORIZATIONS, APPLICABLE_ROLES, ENABLED_ROLES, ROLE_COLUMN_GRANTS.

CHECK_CONSTRAINTS Views

The INFORMATION_SCHEMA.CHECK_CONSTRAINTS view has been added to query CHECK constraints. It provides information about the owner, schema, table, name, and check condition of constraints owned by a user or role. CHECK_CLAUSE provides the check condition expression and returns NULL for NOT NULL constraints.

For more information, reefer to CHECK_CONSTRAINTS.

INFORMATION_SCHEMA Trigger Information Views

The following INFORMATION_SCHEMA views have been added to query trigger definitions, execution order, and dependent objects.

For more information, refer to TRIGGERED_UPDATE_COLUMNS, TRIGGERS, TRIGGER_EVENT_ORDER, TRIGGER_TABLE_USAGE.

Performance and Internal Status Views

The following PERFORMANCE_VIEW_SCHEMA views have been added to query operational status.

For more information, refer to V$DB_PROPERTY, V$RELATION, V$TCL_LOGFILE, V$ALLOCATOR, V$CLUSTER_CONNECTION, V$CLUSTER_QUEUE, V$UNDO_SEGMENT.

RECOVERY_SLAVES

The RECOVERY_SLAVES property has been added to control the number of slave threads used in parallel recovery. The default value is 8. If set to 0, recovery is performed using only the master thread, without any slave threads. If the parallel recovery clause or the number of PARALLEL is omitted from ALTER DATABASE RECOVER, the value set in RECOVERY_SLAVES is used.

For more information, refer to RECOVERY_SLAVES, <recovery slaves clause>.

Histogram Generation Control Properties

The following properties have been added to control the number of histogram buckets and samples when ANALYZE TABLE is executed.

The default value for both BUCKET_COUNT properties is 0. If either value is 5 or less, the corresponding histogram is not created. The recommended value in the manual is 20.

For more information, refer to HISTOGRAM_BALANCE_BUCKET_COUNT, HISTOGRAM_BALANCE_MAX_SAMPLE_COUNT, HISTOGRAM_FREQUENCY_BUCKET_COUNT.

BUFFER_LRU_SCAN_PERCENT

The BUFFER_LRU_SCAN_PERCENT property has been added to manage buffers for disk tablespaces. When no free buffers are immediately available, this property specifies the percentage of pages to scan in the LRU list to find buffers that can be reused, based on the number of buffer pages configured in BUFFER_CACHE_SIZE. The default value is 40%.

For more information, refer to BUFFER_LRU_SCAN_PERCENT.

FULL_TABLE_SCAN_CACHING_THRESHOLD

The FULL_TABLE_SCAN_CACHING_THRESHOLD property has been added to determine whether to use the buffer cache during a full scan of a disk table. Caching is determined by comparing the number of pages used by the table with a threshold calculated based on BUFFER_CACHE_SIZE.

The default value is 20, which enables caching for tables that use pages equal to or less than 2.0% of BUFFER_CACHE_SIZE. A value of 1000 represents 100%, enabling caching for all tables scanned in full.

For more information, refer to FULL_TABLE_SCAN_CACHING_THRESHOLD.

INST_HASH_TABLE_BUCKET_MAX_COUNT

The INST_HASH_TABLE_BUCKET_MAX_COUNT property has been added to specify the upper limit for the estimated bucket count of a hash instant table. This property can be changed at the session level. Changes made using ALTER SYSTEM are applied according to the DEFERRED mode.

For more information, refer to INST_HASH_TABLE_BUCKET_MAX_COUNT.

GLOBAL_TRANSACTION_LOG_BLOCK_SIZE

The GLOBAL_TRANSACTION_LOG_BLOCK_SIZE property has been added to specify the block size of global transaction log files. The unit is bytes, and the value must be one of 512, 1024, 2048, or 4096. The default value is 512, and this property cannot be changed.

For more information, refer to GLOBAL_TRANSACTION_LOG_BLOCK_SIZE.

SHARED_SESSION_MEMORY_INIT_SIZE

The SHARED_SESSION_MEMORY_INIT_SIZE property has been added to specify the initial memory size for shared sessions. The default value is 786432 (768K). This property is distinct from SESSION_MEMORY_INIT_SIZE, which specifies the initial memory size for dedicated sessions.

For more information, refer to SHARED_SESSION_MEMORY_INIT_SIZE.

Instant Index Reference Column Threshold

The INSTANT_INDEX_REFERENCE_COLUMN_THRESHOLD property has been added to reduce the storage space used by instant indexes. If the size of a column stored in an instant index is greater than or equal to the configured threshold, the column is stored by reference.

If an instant index key exceeds the 16,000-byte limit, this property can be adjusted to reduce the key size. Using reference-based storage can reduce index space usage, but may degrade performance.

For more information, refer to INSTANT_INDEX_REFERENCE_COLUMN_THRESHOLD.

REDO_LOGGING_THROTTLING

The REDO_LOGGING_THROTTLING property has been added to control the redo logging rate during index creation and rebuilding. It is intended to reduce the impact of large volumes of log writes on the system and online services. If the number of dirty blocks in the log buffer exceeds the configured value, the operation waits until the blocks are flushed to disk.

For more information, refer to REDO_LOGGING_THROTTLING.

LOG_FLUSHER_HOT_POLICY_INTERVAL

The LOG_FLUSHER_HOT_POLICY_INTERVAL property has been added to control the busy-waiting duration of the log flusher thread in gmaster while waiting for an event. The unit is microseconds, and the default value is 0, which disables busy waiting. Increasing this value can speed up flushing the log buffer to disk, but it also increases CPU usage.

For more information, refer to LOG_FLUSHER_HOT_POLICY_INTERVAL.

ARCHIVE_LOG_THROTTLING

The ARCHIVE_LOG_THROTTLING property has been added to control disk I/O during redo log archiving. Each time the amount of data copied to the archive destination file exceeds the configured value, the process sleeps once to throttle I/O during the copy operation.

For more information, refer to ARCHIVE_LOG_THROTTLING.

SQL and PSM

CURRENT_ROLE and LOCAL_MEMBER_POSITION

The system information functions CURRENT_ROLE and LOCAL_MEMBER_POSITION() have been added. CURRENT_ROLE returns the role set for the current session, while LOCAL_MEMBER_POSITION() returns the cluster member position of the server processing the user query. LOCAL_MEMBER_POSITION() can be used to identify the executing member in a cluster system.

For more information, refer to CURRENT_ROLE, LOCAL_MEMBER_POSITION.

GROUPING and GROUPING_ID

The GROUPING and GROUPING_ID functions have been added. They are used with GROUP BY and return an integer representing a bit value in which each argument is set to 0 if it is a current grouping key, or 1 otherwise. This allows the grouping status of subtotal and grand total rows to be distinguished. GROUPING_ID behaves the same as GROUPING and accepts up to 31 arguments

For more information, refer to GROUPING, GROUPING_ID.

Regular Expression Functions and REGEXP_LIKE Condition

The following functions and condition that use regular expressions have been added. A target string and a pattern must be specified. The pattern can be up to 512 bytes long.

For more information, refer to REGEXP_COUNT, REGEXP_INSTR, REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR.

PRETTY Option for JSON Output

The PRETTY option has been added to the JSON Output Clause of the JSON String Constructor. To change the output format, specify PRETTY after the RETURNING clause, which defines the result type of the JSON string. The return type can be specified as CHAR(n), VARCHAR(n), or LONG VARCHAR.

For more information, refer to JSON Output Clause.

Controlling Duplicate JSON Object Keys

A JSON key uniqueness constraint option has been added to JSON_OBJECT, JSON_OBJECTAGG, and JSON_OBJECTAGG() OVER. WITH UNIQUE KEYS returns an error if a JSON object contains duplicate keys, while WITHOUT UNIQUE KEYS allows duplicate keys. This option allows the duplicate key handling policy to be specified within the function used to generate the JSON object.

For more information, refer to JSON Key Uniqueness Constraint, JSON_OBJECT, JSON_OBJECTAGG, JSON_OBJECTAGG() OVER.

ORDER BY in JSON Array Aggregation

A JSON array aggregate ORDER BY clause option has been added to JSON_ARRAYAGG and JSON_ARRAYAGG() OVER. The function generates a JSON array after sorting values using the ORDER BY clause specified within the function. ASC or DESC and NULLS FIRST or NULLS LAST can be specified. The defaults are ASC and NULLS LAST.

For more information, refer to JSON Array Aggregate Order By Clause, JSON_ARRAYAGG, JSON_ARRAYAGG() OVER.

APPROX_COUNT_DISTINCT

The approximate aggregate function APPROX_COUNT_DISTINCT has been added. It estimates the number of distinct non-NULL values using the HyperLogLog algorithm. It is useful for estimating the number of distinct values in large datasets. The returned value may differ from the exact count returned by COUNT(DISTINCT ...). When FILTER is specified, only values that satisfy the filter condition are included in the aggregation.

For more information, refer to APPROX_COUNT_DISTINCT.

Object-Specific Page Fragmentation Statistics Functions

The TABLE_PHYSICAL_STATS, INDEX_PHYSICAL_STATS, and GSI_PHYSICAL_STATS functions have been added to retrieve page fragmentation statistics. TABLE_PHYSICAL_STATS analyzes regular tables, INDEX_PHYSICAL_STATS analyzes indexes, and GSI_PHYSICAL_STATS analyzes global secondary indexes (GSIs) of clustered tables. The target name must be specified as an identifier, not as a string.

All three functions return page, used, and fragmented information as VARCHAR values. The optional sampling_ratio_value argument specifies the percentage of pages to analyze. It accepts values from 1 to 100 and defaults to 100. When sampling is used, used and fragmented are calculated based on the sampled pages, while page returns the total number of allocated pages.

For more information, refer to TABLE_PHYSICAL_STATS, INDEX_PHYSICAL_STATS, GSI_PHYSICAL_STATS.

ROLE Object DDL

DDL statements for creating and dropping roles and granting and revoking roles to and from users or other roles have been added. No privileges are automatically granted to a newly created role, so the required privileges must be granted separately.

For more information, refer to CREATE ROLE, DROP ROLE, GRANT role TO, REVOKE role FROM.

Histogram Construction in ANALYZE TABLE

ANALYZE TABLE can now build height-balanced and frequency histograms. Setting HISTOGRAM_BALANCE_BUCKET_COUNT enables the construction of a height-balanced histogram, while setting HISTOGRAM_FREQUENCY_BUCKET_COUNT enables the construction of a frequency histogram. Both properties default to 0, and the corresponding histogram is not constructed if the property value is 5 or less. The recommended value is 20. A frequency histogram is not created if the number of required buckets exceeds the configured value.

For more information, refer to ANALYZE TABLE, HISTOGRAM_BALANCE_BUCKET_COUNT, HISTOGRAM_FREQUENCY_BUCKET_COUNT.

FOR COLUMN GROUPS in ANALYZE TABLE

The FOR COLUMN GROUPS clause has been added to ANALYZE TABLE. It collects NUM_DISTINCT statistics for specified column combinations, which can be used for statistical analysis of conditions involving multiple columns. Each column group must contain 2 to 4 columns, and the same column cannot be specified more than once. Column groups that differ only in column order are treated as the same column group.

For more information, refer to FOR COLUMN GROUPS, ANALYZE TABLE.

Improved Performance and Accuracy of ANALYZE Sampling

ANALYZE sampling has been enhanced with repeatable random sampling, approximate NUM_DISTINCT estimation (hyper log algorithm), and NUM_DISTINCT correction using an exponential curve. For example, using 10% sampling can produce the same execution plans as a full ANALYZE for all 22 TPC-H queries.

The ESTIMATE STATISTICS clause of ANALYZE TABLE allows the number or percentage of rows to be sampled to be specified. The percentage specified by SAMPLE percentage PERCENT must be an integer between 1 and 99. If the number of sampled rows is less than MIN_SAMPLE_ROW_COUNT, the value of that property is used.

For more information, refer to ESTIMATE STATISTICS.

Role Auditing in Audit Policies

The role_audit_clause has been added to CREATE AUDIT POLICY and ALTER AUDIT POLICY. The ROLES role_name clause specifies that SQL statements executed using a particular role are subject to auditing. The created policy must be enabled using AUDIT POLICY. The AUDIT SYSTEM ON DATABASE privilege is required to create or modify audit policies. Even for SQL statements executed by the same user, audit results may differ depending on whether the operations are performed using the object's owner privileges or role privileges.

For more information, refer to role_audit_clause, ALTER AUDIT POLICY.

Renaming a Change Tracking File

The ALTER DATABASE RENAME CHANGE TRACKING FILE statement has been added. A new name can be specified for a change tracking file using this statement. The database must be in the MOUNT phase, and change tracking must be enabled. The ALTER DATABASE ON DATABASE privilege is required to execute this statement. If an absolute path is not specified for the file name, the path is constructed by combining SYSTEM_TABLESPACE_DIR with the file name.

For more information, refer to ALTER DATABASE RENAME CHANGE TRACKING FILE.

CHECK Constraints

CHECK constraints have been added. A logical expression can be defined in CREATE TABLE, ALTER TABLE name ADD COLUMN, and ALTER TABLE name ADD CONSTRAINT to specify the condition that each row must satisfy. A condition is considered violated if it evaluates to FALSE, while TRUE or UNKNOWN is considered to satisfy the condition.

Subqueries, references to other objects, nondeterministic expressions, and pseudo-columns are not allowed in the condition. The maximum number of referenced columns is 32. To restrict NULL values as well, the handling of UNKNOWN results must be taken into account.

For more information, refer to CHECK constraint, CREATE TABLE, ALTER TABLE name ADD COLUMN, ALTER TABLE name ADD CONSTRAINT.

FOREIGN KEY Constraints

FOREIGN KEY constraints have been added to define referential relationships in CREATE TABLE, ALTER TABLE name ADD COLUMN, and ALTER TABLE name ADD CONSTRAINT. Foreign key values must exist in the referenced table's PRIMARY KEY or UNIQUE constraint. However, foreign key values containing NULL are allowed as an exception.

For column constraints, the REFERENCES clause is used. For table constraints, the FOREIGN KEY (...) REFERENCES clause is used. Foreign keys can be defined only on base tables, not on temporary tables or views.

For more information, refer to FOREIGN KEY constraint, CREATE TABLE, ALTER TABLE name ADD COLUMN, ALTER TABLE name ADD CONSTRAINT.

Enabling and Disabling Constraints

The constraint enforcement option is now available in ALTER TABLE name ALTER CONSTRAINT to change the enforcement status of a constraint. ENFORCED enables the constraint, while NOT ENFORCED disables it. The constraint to be modified can be specified by its name or by the corresponding UNIQUE constraint associated with a PRIMARY KEY or a column list.

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

Enabling and Disabling Indexes

The ALTER INDEX name ENABLE/DISABLE statement has been added to control index enforcement. ENABLE builds and enables the index, while DISABLE disables the index and releases all extents used by it. Disabled indexes are not used for DML or SELECT operations.

ENABLE and ENFORCED perform the same operation, as do DISABLE and NOT ENFORCED. Indexes created for key constraints must be managed using ALTER CONSTRAINT.

For more information, refer to ALTER INDEX name ENABLE/DISABLE.

Specifying Table Trigger Execution Order

The ALTER TABLE name SET TRIGGER ORDER statement has been added to change the execution order of triggers by rearranging the names of triggers defined on a table. If only some triggers are specified, they are executed first in the specified order, while the remaining triggers retain their existing order. All listed triggers must belong to the same table and have the same timing (BEFORE or AFTER) and execution granularity (ROW or STATEMENT).

For more information, refer to ALTER TABLE name SET TRIGGER ORDER.

ALTER TABLE name REORGANIZE

The ALTER TABLE name REORGANIZE statement has been added to physically reorganize a table. The ONLINE option allows INSERT, UPDATE, and DELETE operations during reorganization, while the OFFLINE option does not. The default is ONLINE. The SCAN PARTITION and PARALLEL options can be used to specify partition scanning and parallel processing.

The default redo logging mode is LOGGING. When NOLOGGING is specified, the table may become unusable if the server terminates abnormally after reorganization. Therefore, when using NOLOGGING, a CHECKPOINT must be performed after reorganization, as described in the manual.

For more information, refer to ALTER TABLE name REORGANIZE.

MERGE

The MERGE statement has been added. It compares a source specified by USING with a target table based on the ON condition. If a match is found, UPDATE, DELETE, or DO NOTHING is performed; otherwise, INSERT or DO NOTHING is performed. Multiple data modification operations can be specified in a single statement, each with its own condition.

For each target row, the WHEN clauses are evaluated in the order in which they appear, and only the first matching branch is executed. No separate MERGE privilege is required. SELECT privilege on the referenced columns and the privileges required for the INSERT, UPDATE, and DELETE operations specified in the statement are required.

For more information, refer to MERGE.

APPEND INSERT and Hints

The APPEND INSERT data loading method and the hint used to specify it have been added. This method appends data to unused or newly allocated space, making it suitable for bulk data loading. For disk tables, data is written directly to the data files without passing through the buffer.

APPEND INSERT can be used with the APPEND hint in INSERT statements, the corresponding options in gloader, or certain bulk synchronization DDL statements. PARALLEL allows multiple sessions to perform APPEND INSERT operations concurrently, while NOLOGGING, when used with this option, reduces the amount of log data generated. STATEMENT_NOFORCE specifies whether the data pages used by a statement are written to disk when the statement completes.
Depending on the specified options, referential integrity checks and index key updates may be handled differently. Therefore, the appropriate options should be selected based on the data loading method.

For more information, refer to Adding Data Using the APPEND INSERT Method.

Local Shard Limit in DELETE FROM

A local shard limit clause has been added to DELETE FROM to limit the number of records deleted. For a sharded table in a cluster, this clause deletes up to limit_row_count records from the local group. It can be used only with sharded tables, and limit_row_count must be a positive value. This clause cannot be used with the result offset clause or the fetch limit clause.

For more information, refer to local shard limit clause.

ROLLUP·CUBE·GROUPING SETS in GROUP BY

Support for ROLLUP, CUBE, and GROUPING SETS has been added to the grouping elements of GROUP BY. ROLLUP progressively reduces the listed grouping columns to calculate subtotals and a grand total. CUBE calculates all possible grouping combinations, while GROUPING SETS explicitly specifies only the required combinations. These features allow subtotal configurations to be selected according to aggregation requirements.

For more information, refer to group by clause.

Referencing SELECT Aliases in GROUP BY

GROUP BY grouping column references can now refer to aliases defined in the SELECT list. An alias assigned to a column in the SELECT list can be used as a grouping key. If multiple aliases have the same name, the reference is ambiguous. If an alias has the same name as an actual column, the actual column reference takes precedence.

SELECT COUNT(*), t1.c1 AS A1 FROM t1 GROUP BY A1;

For more information, refer to group by clause.

PIVOT Clause

The PIVOT clause has been added to convert row values into columns. A cross table can be created by specifying an aggregate function, a pivot column in the FOR clause, and a list of values in the IN clause of a table reference in the FROM clause. Aggregate functions cannot be nested, and the number of columns specified in the FOR clause must match the number of elements in each value combination listed in the IN clause.

For more information, refer to pivot clause.

UNPIVOT Clause

The UNPIVOT clause has been added to convert column values into rows. The IN clause specifies the names of the source columns to be unpivoted. The names of the columns that store the converted values and identify the source columns are defined using the value column and the FOR column, respectively. To include NULL values in the result, specify INCLUDE NULLS. If omitted, EXCLUDE NULLS is applied by default.

For more information, refer to unpivot clause.

TABLESAMPLE Clause

The SAMPLE clause has been added to randomly sample a subset of rows from a table. TABLESAMPLE specifies a sampling percentage greater than 0 and less than or equal to 100. The sampling unit can be selected using PERCENT ROWS or PERCENT PAGES. If the sampling unit is omitted, PERCENT ROWS is applied by default. A seed value can be specified using REPEATABLE(seed_value). If no seed value is specified, the sampling results may vary between executions.

For more information, refer to sample clause.

FILTER for Aggregate Functions

The FILTER feature has been added to aggregate functions. By specifying FILTER (WHERE condition) after an aggregate function call, only values that satisfy the condition are included in the aggregation. Different conditions can be specified for individual aggregate functions within a single SELECT statement.

SELECT COUNT(*), COUNT(*) FILTER (WHERE c1 > 1) FROM t1;

For more information, refer to Aggregate Function, COUNT(*) FILTER.

FILTER for JSON Aggregate Functions

The FILTER feature has been added to JSON aggregate constructors. By specifying FILTER (WHERE condition) after JSON_ARRAYAGG or JSON_OBJECTAGG, only values that satisfy the condition are included in the resulting array or object. The aggregation result is returned as a single row containing a JSON string.

For more information, refer to JSON aggregate Constructor, JSON_ARRAYAGG, JSON_OBJECTAGG.

Execution Location Hints for Window Functions and UNION ALL

Window hints and UNION ALL driver hints have been added to SQL hints. In a cluster query, these hints specify whether window functions or UNION ALL operations are processed on the current server or on the servers of each group. These driver hints are ignored in standalone environments.

For more information, refer to window hints, union all driver hints.

ALTER SYSTEM CANCEL SESSION

The ALTER SYSTEM CANCEL SESSION statement has been added to cancel an operation running in a session. The session ID and serial number of the target session must be specified. In a cluster database, the target member position can also be specified. The ALTER SYSTEM ON DATABASE privilege is required to execute this statement.

For more information, refer to ALTER SYSTEM CANCEL SESSION.

SET ROLE

The SET ROLE role_name statement has been added to change the role of the current session. A role that can be applied to the current user can be selected, or a role can be set using the ACCESS CONTROL ON DATABASE privilege. Specifying SET ROLE NONE clears the current role setting. Roles cannot be changed during an active transaction. After the role is changed, statements are executed based on the configured current role.

For more information, refer to SET ROLE role_name.

External Routine

External routines have been added to execute user-written C programs. Functions stored in a shared library can be called from PSM routines. The external routine process, gextproc, loads and executes the library and returns the results to PSM. By defining a library object and a call specification, external functions can be used within the DBMS.

For more information, refer to External Routine, Call Specification.

Trigger Objects

Trigger objects have been added to define actions that execute automatically when DML operations are performed on a specific table. Actions written in PSM can be associated with INSERT, UPDATE, and DELETE events and configured to execute BEFORE or AFTER an event, at either the ROW or STATEMENT level. Enabled triggers execute automatically when the associated event occurs and cannot be invoked explicitly. When the target table is dropped, its dependent triggers are also dropped.

For more information, refer to Trigger Overview, CREATE TRIGGER.

Call Specification

Call specifications have been added to map external C functions to PSM routines. The LANGUAGE C, LIBRARY, NAME, and PARAMETER clauses are used to map the external function name and the data types of its arguments and return value, allowing the function to be called from PSM. The EXECUTE privilege on the target library, or an equivalent privilege specified in the manual, is required to use a call specification.

For more information, refer to Call Specification.

Library Object DDL

The CREATE LIBRARY and DROP LIBRARY statements have been added to manage library objects. CREATE LIBRARY associates a shared library file path for a C program with a schema object name. Specifying OR REPLACE replaces an existing object. DROP LIBRARY removes a library object, and specifying IF EXISTS ensures that the object is dropped only if it exists.

For more information, refer to CREATE LIBRARY, DROP LIBRARY.

Trigger Object DDL

The following DDL statements have been added to create, drop, recompile, enable or disable, and rename trigger objects.

For more information, refer to CREATE TRIGGER, DROP TRIGGER, ALTER TRIGGER name COMPILE, ALTER TRIGGER name ENABLE/DISABLE, ALTER TRIGGER name RENAME TO.

API and Development Environment

Added the commit_write_mode JDBC Connection Property

The commit_write_mode connection property has been added to JDBC. This property specifies how logs generated when a transaction completes are flushed to the log files on disk. A value of 0 uses the server configuration, 1 specifies wait, and 2 specifies no wait. The default value is 0.

For more information, refer to JDBC: connection property.

Added the --parse Option to gpec

The --parse option has been added to gpec, the Embedded SQL precompiler. This option specifies the parsing level for the input source code. none parses only the minimum structure required for execution, while partial parses the source to the extent required for preprocessing. The default value is partial.
For example, it can be used as gpec --parse=none sample.gc.

For more information, refer to Embedded SQL: --parse.

Support for Embedded SQL Dynamic SQL Method 4

Embedded SQL now supports dynamic SQL method 4. This method is used for SQL statements whose number or types of columns to be retrieved, or number of parameters are unknown at precompile time. It provides the ANSI SQL descriptor and SQLDA approaches. These approaches use descriptors to specify input and output information at runtime for PREPARE, EXECUTE, or cursor operations. Neither approach supports array operations using struct, the SQL_CURSOR type, or arrays of the VARCHAR type.

For more information, refer to Embedded SQL: Method 4.

Python 3 Type Stub Support for PyDBC

The PyDBC Python 3 package now includes the pygoldilocks.pyi type stub, which can be used with IDEs and static type checking tools.

For more information, refer to PyDBC: API Reference.

Support for Character Codec Configuration in PyDBC Python 3

PyDBC Python 3 supports connection.setencoding() and setdecoding().
setencoding(encoding, ctype=SQL_CHAR) specifies the codec used to convert SQL statements and string parameters to bytes.
setdecoding(sqltype, encoding, ctype=SQL_CHAR) specifies the codec used to convert character results to Python strings.
Only SQL_CHAR is supported as the argument; wide character types are not supported. These settings persist for the lifetime of the connection and are not reset by executing SQL statements or calling commit() or rollback().

For more information, refer to PyDBC: Character Codec Configuration.

Support for Connection Diagnostics, Character Set Retrieval, and Output Converters in PyDBC Python 3

PyDBC Python 3 supports connection.messages, character_set_name(), and SQL-type-specific output converters. The messages attribute stores DB-API warnings generated during connection operations. This list is cleared when the next connection operation begins, so it should be checked immediately after each operation. The character_set_name() method returns the name of the GOLDILOCKS character set currently configured for the connection.

When an output converter is registered using add_output_converter(sqltype, func), it is applied to all cursors associated with the connection. The conversion function receives raw bytes or None representing SQL NULL, and its return value becomes the corresponding value in the query result row. Converter registrations and modifications take effect starting with the next call to execute().

For more information, refer to PyDBC: Output Converter.

Support for connection.maxwrite in PyDBC

PyDBC supports connection.maxwrite for handling large character and binary parameters. This attribute specifies the threshold size, in bytes, that determines whether parameters are bound directly or sent using SQLPutData.
A value of 0 uses the default threshold determined by the driver and data type. Custom values must be at least 255.
This attribute affects only how input data is transmitted. It does not truncate data or limit the size of query results.

For more information, refer to PyDBC: maxwrite.

Handling Multiple Result Sets with cursor.nextset() in PyDBC

Multiple result sets returned by a procedure can be processed sequentially using cursor.nextset(). Rows in the current result set can be retrieved using fetch methods such as fetchone(). Calling nextset() advances to the next result set. It returns True if another result set is available, or None if no more result sets remain.

For more information, refer to PyDBC: nextset().

Added Procedure Metadata Retrieval with procedureColumns() in PyDBC

PyDBC now provides cursor.procedureColumns(procedure=None, catalog=None, schema=None). This method retrieves metadata for a procedure's IN, OUT, and INOUT parameters, return values, and result columns through the ODBC SQLProcedureColumns function.
The returned metadata includes information such as column_name, column_type, data_type, type_name, column_size, and ordinal_position.

For more information, refer to PyDBC: procedureColumns().

Support for fetchval(), Cursor Iteration, and Statement Cancellation in PyDBC

PyDBC supports fetchval(), cursor iteration, and statement cancellation.
The fetchval() method returns the first column value of the next row, or None if no more rows are available.
Cursor iteration retrieves and processes result rows one at a time.
The cursor.cancel() method requests cancellation of the statement currently executing on that cursor. Exceptions may occur during cancellation, so after handling any exceptions, verify whether the cursor can be reused.

For more information, refer to PyDBC: fetchval() and cancel().

Utility

Role DDL Output in gsql/gsqlnet

The \ddl_role command has been added to output the DDL for a role object. Running \ddl_role name outputs all statements related to creating the role, granting privileges, and adding comments.
To output only specific statements, specify the CREATE, GRANT, or COMMENT option.

For more information, refer to Role DDL Output in gsql/gsqlnet.

Trigger DDL Output in gsql/gsqlnet

The \ddl_trigger command has been added to output the DDL for a trigger object. Running \ddl_trigger name outputs the statements for creating the trigger and adding comments.
To output only specific statements, specify the CREATE or COMMENT option. Objects stored in the recycle bin are excluded from the output.

For more information, refer to Trigger DDL Output in gsql/gsqlnet.

APPEND INSERT Options in gloader/gloadernet

The APPEND INSERT method for loading data and the --merge, --skip_index_maintenance, and --nologging options have been added. During import operations, --merge EXTENT loads data using serial APPEND INSERT, while --merge SEGMENT loads data using PARALLEL APPEND INSERT.

The existing --append option specifies whether existing data is retained, deleted, or truncated before loading. It serves a different purpose from the newly added --merge option.

For more information, refer to APPEND INSERT Options in gloader/gloadernet.

Replication

Data Migration to Other DBMSs Using CYCLONE

The feature for migrating data to Oracle, DB2, MySQL, and Tibero using CYCLONE has been added. The CYCLONE master analyzes changes in the source GOLDILOCKS redo log and transmits them to the slave, which applies the changes to the target DBMS using ODBC.

Depending on the target DBMS, specify the path to the corresponding ODBC driver file using the ORACLE_DRIVER, DB2_DRIVER, MYSQL_DRIVER, or TIBERO_DRIVER setting on the slave. For MySQL and DB2, specify the target database using MYSQL_DATABASE or DB2_DATABASE, respectively. For SYNC operations, use the corresponding SYNC_ORACLE_DRIVER, SYNC_DB2_DRIVER, SYNC_MYSQL_DRIVER, or SYNC_TIBERO_DRIVER setting on the master.

The column data types in GOLDILOCKS and the target DBMS must match. When integrating with another DBMS, DDL processing during CYCLONE replication is not supported.

For more information, refer to Data Migration to Other DBMSs Using CYCLONE.

Separation of the CYCLONE Heartbeat Protocol

The CYCLONE heartbeat protocol has been separated from the data protocol. The connection timeout is configured using the existing HEARTBEAT_TIMEOUT setting. This setting is available on both the master and slave and specifies the maximum time, in seconds, to maintain a connection when communication is disrupted due to network disconnections or system failures. The default value is 30 seconds, and the minimum value is 10 seconds.

For more information, refer to Separation of the CYCLONE Heartbeat Protocol.