Initialization Parameters

Autonomous AI Database configures database initialization parameters automatically when you provision a database. You do not need to set any initialization parameters to start using your service. But, you can modify some parameters if you need to.

Modifiable Initialization Parameters

The following table shows the initialization parameters that are only modifiable with ALTER SESSION.

Note: Not all of these settings are Oracle Database initialization parameters. Some are session-specific attributes, ALTER SESSION options, or SQL Translation Framework settings. These settings do not appear in V$PARAMETER and cannot be queried with SHOW PARAMETER.

Session-specific attributes include:

Additional ALTER SESSION options include:

SQL Translation Framework settings includes SQL_TRANSLATION_PROFILE.

Refer to the Oracle AI Database 26ai SQL Language Reference - ALTER SESSION for details on the semantics and usage of these settings.

Only Modifiable with ALTER SESSION More Information
CONSTRAINTS  
CONTAINER  
CURRENT_SCHEMA  
CURSOR_INVALIDATION CURSOR_INVALIDATION
DEFAULT_COLLATION  
DEFAULT_CREDENTIAL  
EDITION  
ISOLATION_LEVEL  
JSON_BEHAVIOR This parameter is only applicable with Oracle AI Database 26ai. See JSON_BEHAVIOR for more information.
JSON_EXPRESSION_CHECK JSON_EXPRESSION_CHECK
OPTIMIZER_SESSION_TYPE OPTIMIZER_SESSION_TYPE
OPTIMIZER_USE_INVISIBLE_INDEXES OPTIMIZER_USE_INVISIBLE_INDEXES
READ_ONLY  
SQL_TRACE See Perform SQL Tracing on Autonomous AI Database for details
SQL_TRANSLATION_PROFILE  
SQL_TRANSPILER This parameter is only applicable with Oracle AI Database 26ai. See SQL_TRANSPILER for more information.
STATISTICS_LEVEL STATISTICS_LEVEL
TIME_ZONE For more information on TIME_ZONE, see Oracle AI Database SQL Language Reference.
XML_PARAMS This parameter is only applicable with Oracle AI Database 26ai. See XML_PARAMS for more information.

The following table shows the initialization parameters that are only modifiable with ALTER SYSTEM.

Only Modifiable with ALTER SYSTEM More Information
BLANK_TRIMMING BLANK_TRIMMING
FIXED_DATE FIXED_DATE
JOB_QUEUE_PROCESSES JOB_QUEUE_PROCESSES
LOCKDOWN_ERRORS See LOCKDOWN_ERRORS for details
MAX_IDLE_BLOCKER_TIME MAX_IDLE_BLOCKER_TIME
With a value higher than 5, the parameter acts as if it was set to 5
MAX_IDLE_TIME MAX_IDLE_TIME
By default MAX_IDLE_TIME is set 1440. Setting the MAX_IDLE_TIME value to 0 or to a value higher than 1440, the parameter acts as if it was set to 1440.
RESOURCE_MANAGER_PLAN Specifies which resource plan to use for an Autonomous AI Database.
SESSION_EXIT_ON_PACKAGE_STATE_ERROR SESSION_EXIT_ON_PACKAGE_STATE_ERROR

The following table shows the initialization parameters that are modifiable with either ALTER SESSION or ALTER SYSTEM.

Modifiable with ALTER SESSION or ALTER SYSTEM More Information
APPROX_FOR_AGGREGATION APPROX_FOR_AGGREGATION
APPROX_FOR_COUNT_DISTINCT APPROX_FOR_COUNT_DISTINCT
APPROX_FOR_PERCENTILE APPROX_FOR_PERCENTILE
CLIENT_PREFETCH_ROWS See CLIENT_PREFETCH_ROWS
CLOUD_TABLE_COMMIT_THRESHOLD For more information see CLOUD_TABLE_COMMIT_THRESHOLD.
CONTAINER_DATA CONTAINER_DATA
CURSOR_SHARING CURSOR_SHARING
DDL_LOCK_TIMEOUT DDL_LOCK_TIMEOUT
GROUP_BY_POSITION  
GROUP_BY_POSITION_ENABLED This parameter is only applicable with Oracle AI Database 26ai. See GROUP_BY_POSITION_ENABLED for more information
HEAT_MAP HEAT_MAP
IGNORE_SESSION_SET_PARAM_ERRORS IGNORE_SESSION_SET_PARAM_ERRORS
LDAP_DIRECTORY_ACCESS LDAP_DIRECTORY_ACCESS
LOAD_WITHOUT_COMPILE  
MAX_STRING_SIZE See Data Types for details
NLS_CALENDAR NLS_CALENDAR
NLS_COMP NLS_COMP
NLS_CURRENCY NLS_CURRENCY
NLS_DATE_FORMAT NLS_DATE_FORMAT
NLS_DATE_LANGUAGE NLS_DATE_LANGUAGE
NLS_DUAL_CURRENCY NLS_DUAL_CURRENCY
NLS_ISO_CURRENCY NLS_ISO_CURRENCY
NLS_LANGUAGE NLS_LANGUAGE
NLS_LENGTH_SEMANTICS NLS_LENGTH_SEMANTICS
NLS_NCHAR_CONV_EXCP NLS_NCHAR_CONV_EXCP
NLS_NUMERIC_CHARACTERS NLS_NUMERIC_CHARACTERS
NLS_SORT NLS_SORT
NLS_TERRITORY NLS_TERRITORY
NLS_TIME_FORMAT  
NLS_TIME_TZ_FORMAT  
NLS_TIMESTAMP_FORMAT NLS_TIMESTAMP_FORMAT
NLS_TIMESTAMP_TZ_FORMAT NLS_TIMESTAMP_TZ_FORMAT
OPTIMIZER_CAPTURE_SQL_QUARANTINE OPTIMIZER_CAPTURE_SQL_QUARANTINE
OPTIMIZER_IGNORE_HINTS For more information on OPTIMIZER_IGNORE_HINTS, see Manage Optimizer Statistics on Autonomous AI Database.
OPTIMIZER_IGNORE_PARALLEL_HINTS For more information on OPTIMIZER_IGNORE_PARALLEL_HINTS, see Manage Optimizer Statistics on Autonomous AI Database.
OPTIMIZER_MODE OPTIMIZER_MODE
OPTIMIZER_REAL_TIME_STATISTICS OPTIMIZER_REAL_TIME_STATISTICS
OPTIMIZER_USE_SQL_QUARANTINE OPTIMIZER_USE_SQL_QUARANTINE
PLSCOPE_SETTINGS PLSCOPE_SETTINGS
PLSQL_CCFLAGS PLSQL_CCFLAGS
PLSQL_DEBUG PLSQL_DEBUG
PLSQL_IMPLICIT_CONVERSION_BOOL This parameter is only applicable with Oracle AI Database 26ai. See PLSQL_IMPLICIT_CONVERSION_BOOL
PLSQL_OPTIMIZE_LEVEL PLSQL_OPTIMIZE_LEVEL
PLSQL_WARNINGS PLSQL_WARNINGS
QUERY_REWRITE_INTEGRITY QUERY_REWRITE_INTEGRITY
RECYCLEBIN RECYCLEBIN
REMOTE_DEPENDENCIES_MODE REMOTE_DEPENDENCIES_MODE
RESULT_CACHE_INTEGRITY See RESULT_CACHE_INTEGRITY
RESULT_CACHE_MODE See RESULT_CACHE_MODE
SKIP_UNUSABLE_INEDEXES SKIP_UNUSABLE_INDEXES
SQL_HISTORY_ENABLED This parameter is available starting with Oracle AI Database 26ai. See SQL_HISTORY_ENABLED for more information.
SYSDATE_AT_DBTIMEZONE See SYSDATE_AT_DBTIMEZONE Select a Time Zone for SYSDATE on Autonomous AI Database
XML_CLIENT_SIDE_DECODING See XML_CLIENT_SIDE_DECODING

For more information on initialization parameters see Oracle AI Database Reference.

CLIENT_PREFETCH_ROWS

Set the CLIENT_PREFETCH_ROWS parameter to enable clients to reduce the number of roundtrips required while fetching rows of a query result set.

CLIENT_PREFETCH_ROWS specifies the number of rows to be prefetched by the Oracle client driver, without making any changes to the client application. The client driver buffers the prefetched rows after each successful query execution and for each subsequent fetch request sent to the database.

This parameter applies only to clients that use Oracle Call Interface (OCI) to connect to the database.

This parameter applies only with Oracle Instant Client/Oracle Database Client 19.17 (or later) and 21.8 (or later), for all platforms.

Property Description
Parameter type Integer
Syntax CLIENT_PREFETCH_ROWS = integer
Default Value 0 (Only client-side settings apply)
Modifiable ALTER SYSTEM, ALTER SESSION
Range of values 0 to UB4MAXVAL (4294967295)
Basic No

The CLIENT_PREFETCH_ROWS parameter can be set with ALTER SESSION or ALTER SYSTEM. If the parameter value changes using ALTER SESSION, the new value becomes effective for that specific session on subsequent resultset fetches. If the parameter value changes using ALTER SYSTEM, the new value takes effect for the statements that run on connections created after the ALTER SYSTEM command.

For example, if CLIENT_PREFETCH_ROWS is set to 100 and a client application asks to fetch 10 rows, a total of 110 rows are returned to the client driver. The first 10 rows out of the 110 rows are given to the application, and the client driver internally buffers the remaining 100 rows. The next 10 row fetches from the client application, each with 10 rows per fetch iteration can be fulfilled from the 100 rows that are internally buffered by the client driver. This process reduces the number of required network roundtrips to and from the database. In this example, on the 11th fetch, a new network roundtrip is incurred and the database returns the next batch of 110 rows, as long as the result set is not exhausted, and the cycle repeats.

Notes for setting CLIENT_PREFETCH_ROWS:

CLOUD_TABLE_COMMIT_THRESHOLD

The CLOUD_TABLE_COMMIT_THRESHOLD parameter controls the buffered change count for Cloud Tables before changes are written to object storage.

When the CLOUD_TABLE_COMMIT_THRESHOLD parameter is set to a non-zero value, the system treats the value as a change count threshold and Cloud Table changes are buffered until the number of changes reaches the specified threshold. When the threshold is reached the buffered changes are exported to Object Storage. The default value of this parameter is 0 (buffering is disabled).

See About Cloud Tables for more information.

Note: This parameter applies for all Cloud Tables, and can be set by any user with the ALTER SESSION privilege. Therefore, Cloud Tables are not suitable for security-critical data where committed changes must be durable and must be immediately visible to concurrent readers. For this reason, Cloud Tables may not be appropriate for use cases such as audit log tables.

Property Description
Parameter type Integer
Syntax CLOUD_TABLE_COMMIT_THRESHOLD = integer
Default Value The default value is 0 (buffering is disabled).
Modifiable ALTER SESSION, ALTER SYSTEM
Range of values 0 to 2147483647

JOB_QUEUE_PROCESSES

Set the JOB_QUEUE_PROCESSES parameter to specify the maximum number of job workers that can be created to run Oracle Scheduler (DBMS_SCHEDULER) jobs.

Setting the value to 0 disables non-Oracle supplied Scheduler jobs.

Property Description
Parameter type Integer
Syntax JOB_QUEUE_PROCESSES = integer
Default Value The default value depends on the ECPU count (OCPU count if your database uses OCPUs) and the setting for ECPU auto scaling (OCPU auto scaling if your database uses OCPUs). See the following table for details.
Modifiable ALTER SYSTEM
Range of values

Minimum value: 0

Maximum value depends on the ECPU count (OCPU count if your database uses OCPUs) and the setting for ECPU auto scaling (OCPU auto scaling if your database uses OCPUs), as shown in the following table.

The default and maximum values for JOB_QUEUE_PROCESSES differ depending on the compute model you use. See Compute Models in Autonomous AI Database for more information.

Compute Model Default and Maximum Value with Auto Scaling Disabled Default and Maximum Value with Auto Scaling Enabled
ECPU 7.5 x ECPU count 22.5 x ECPU count
OCPU 30 x OCPU count 90 x OCPU count

Oracle Scheduler job coordinator and job workers are controlled by the JOB_QUEUE_PROCESSES parameter. The actual number of job workers created for Oracle Scheduler jobs is auto-tuned by the Scheduler depending on several factors, including available resources, Resource Manager settings, and currently running jobs.

The default value for JOB_QUEUE_PROCESSES provides a compromise between quality of service for applications and reasonable use of system resources. However, it is possible that the default value does not suit every environment.

Setting the value of JOB_QUEUE_PROCESSES to 0 disables non-Oracle supplied Scheduler jobs. When JOB_QUEUE_PROCESSES is set to 0 this does not disable any internal jobs for Oracle-supplied users (service related jobs run by Oracle-supplied users continue to be scheduled).

Oracle-supplied users are users marked as ORACLE_MAINTAINED with value Y. Non Oracle-supplied users are users marked as ORACLE_MAINTAINED with value N.

See ALL_USERS for more information.

LOCKDOWN_ERRORS

Set the LOCKDOWN_ERRORS parameter to control whether Autonomous AI Database lockdown profile errors are raised or ignored.

Autonomous AI Database uses lockdown profiles to restrict certain database functionality and features. By default the database raises an error whenever you attempt to run a SQL command that is restricted by the Autonomous AI Database lockdown profile. The LOCKDOWN_ERRORS parameter allows you to suppress lockdown profile errors. Additionally, when LOCKDOWN_ERRORS is set to the value IGNORE, upon ignoring an error the database logs the details regarding the SQL statement, user, and timestamp for the command.

Note: Carefully evaluate your existing scripts before you set the value of this parameter to IGNORE. When you set the value to IGNORE, any existing scripts you use will no longer raise lockdown errors.

You can use DBA_LOCKDOWN_ERRORS view to obtain information on lockdown profile errors. See Lockdown Errors View for more information.

Property Description
Parameter type String
Syntax LOCKDOWN_ERRORS = IGNORE | RAISE
Default Value The default value is RAISE.
Modifiable ALTER SYSTEM
Valid values

RAISE: Raise lockdown profile errors.

IGNORE: Suppress lockdown profile errors.

SESSION_EXIT_ON_PACKAGE_STATE_ERROR

SESSION_EXIT_ON_PACKAGE_STATE_ERROR enables or disables special handling for stateful PL/SQL packages running in a session.

Property Description
Parameter type Boolean
Default Value FALSE
Modifiable ALTER SYSTEM
Range of values TRUE | FALSE

SESSION_EXIT_ON_PACKAGE_STATE_ERROR specifies the handling for a stateful PL/SQL package running in a session. When such a package undergoes modification, such as during planned maintenance for Oracle-supplied objects, the sessions that have an active instantiation of the package receive the following error when they attempt to run the package:

ORA-4068 existing state of package has been discarded

However, the application code that receives the ORA-4068 error may not be equipped to handle this error with its retry logic.

Setting SESSION_EXIT_ON_PACKAGE_STATE_ERROR to TRUE provides different handling for this case. When SESSION_EXIT_ON_PACKAGE_STATE_ERROR is TRUE, instead of just raising the ORA-4068 error when the package state is discarded, the session immediately exits. This can be advantageous because many applications are able to handle session termination by automatically and transparently re-establishing the connection.

RESULT_CACHE_INTEGRITY

Set the RESULT_CACHE_INTEGRITY parameter to specify whether the result cache considers queries using possibly non-deterministic constructs as candidates for result caching.

Property Description
Parameter type String
Syntax RESULT_CACHE_INTEGRITY = { ENFORCED | TRUSTED }
Default Value For Autonomous AI Database the default value is: ENFORCED
Modifiable ALTER SYSTEM

Values

RESULT_CACHE_MODE

Set the RESULT_CACHE_MODE parameter to specify which queries are eligible to store result sets in the result cache. Only query execution plans with the result cache operator will attempt to read from or write to the result cache.

Property Description
Parameter type String
Syntax RESULT_CACHE_MODE = { MANUAL | MANUAL_TEMP | FORCE | FORCE_TEMP }
Default Value

For Autonomous AI Database with workload type set to Lakehouse: FORCE

For workload types Transaction Processing, JSON, or APEX: MANUAL
Modifiable ALTER SESSION, ALTER SYSTEM

See RESULT_CACHE_MODE for more information.

Using SQL Result Cache Hints

Use result cache hints at the application level to control caching behavior. The SQL result cache hints take precedence over the result cache mode and result cache table annotations.

You can use SQL result cache hints in the following ways:

Using the RESULT_CACHE Hint

When the result cache mode is MANUAL, the /*+ RESULT_CACHE */ hint instructs the database to cache the results of a query block and to use the cached results in future executions.

See Using the RESULT_CACHE Hint and RESULT_CACHE Hint for more information.

Using the NO_RESULT_CACHE Hint

The /*+ NO_RESULT_CACHE */ hint instructs the database not to cache the results in either the server or client result caches.

See Using the NO_RESULT_CACHE Hint and RESULT_CACHE Hint for more information.

Using the RESULT_CACHE Hint in Views

The RESULT_CACHE hint applies only to the query block in which the hint is specified. If the hint is specified only in a view, then only these results are cached.

See Using the RESULT_CACHE Hint in Views and RESULT_CACHE Hint for more information.

SYSDATE_AT_DBTIMEZONE Select a Time Zone for SYSDATE on Autonomous AI Database

SYSDATE_AT_DBTIMEZONE enables special handling in a session for the date and time value returned in calls to SYSDATE and SYSTIMESTAMP.

Depending on the value of SYSDATE_AT_DBTIMEZONE, you see either the date and time based on the default Autonomous AI Database time zone, Coordinated Universal Time (UTC), or based on the time zone that you set in your database.

Property Description
Parameter type Boolean
Default Value FALSE
Modifiable ALTER SESSION, ALTER SYSTEM
Range of values TRUE | FALSE

Default Autonomous AI Database Time Zone

The default Autonomous AI Database time zone is Coordinated Universal Time (UTC) and by default calls to SYSDATE and SYSTIMESTAMP return the date and time in UTC.

In order to change database time zone, you can run the following statement.

This example sets the database time zone to UTC-5.

ALTER DATABASE SET TIME_ZONE='-05:00';

This example sets the database time zone for the US east coast: America/New_York and the time is automatically adjusted at DST switches:

ALTER DATABASE SET TIME_ZONE='America/New_York';

Note: You must restart the Autonomous AI Database instance for the change to take effect.

After you set the database time zone, by default SYSDATE and SYSTIMESTAMP continue to return date and time in UTC (SYSDATE_AT_DBTIMEZONE is FALSE by default). If you set SYSDATE_AT_DBTIMEZONE to TRUE in a session, SYSDATE and SYSTIMESTAMP return the database time zone.

See Setting the Database Time Zone in Oracle Database 19c or Oracle AI Database 26ai for more information on using the SET TIME_ZONE clause with ALTER DATABASE.

Using SYSDATE_AT_DBTIMEZONE in a Session

When SYSDATE_AT_DBTIMEZONE is FALSE in a session, calls to SYSDATE and SYSTIMESTAMP return values based on the default Autonomous AI Database time zone, Coordinated Universal Time (UTC).

When SYSDATE_AT_DBTIMEZONE is TRUE in a session, calls to SYSDATE or SYSTIMESTAMP return the date and time based on the database time zone.

Note: Setting SYSDATE_AT_DBTIMEZONE to TRUE only affects the use of SYSDATE and SYSTIMESTAMP as operators in application SQL (for example, in queries, DML, and CTAS operations). When using this parameter, it is recommended that your client/session timezone matches your database timezone.

Example

The following example returns dates and times for two different time zones, based on the SYSDATE_AT_DBTIMEZONE parameter value:

SQL> SELECT DBTIMEZONE FROM DUAL;

DBTIMEZONE
_____________
-05:00

SQL> ALTER SESSION SET SYSDATE_AT_DBTIMEZONE=FALSE;

Session altered.

SQL> SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP
______________________________________
27-JAN-22 06.59.45.708082000 PM GMT

SQL> ALTER SESSION SET SYSDATE_AT_DBTIMEZONE=TRUE;

Session altered.

SQL> SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP
_________________________________________
27-JAN-22 02.14.47.578946000 PM -05:00

Note: When a SYSDATE or SYSTIMESTAMP query is executed in SQL Worksheet of Database Actions, the time and date value that is returned is in UTC (when SYSDATE_AT_DBTIMEZONE parameter is set to TRUE or FALSE). To obtain the database time zone when working in Database Actions, use TO_CHAR() as follows:

SQL> SELECT TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD"T"HH24:MI:SS TZH":"TZM') FROM DUAL;
TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD"T"HH24:MI:SSTZH":"TZM')
___________________________________________________________

2022-01-27T14:15:00 -05:00