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.

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 Database 23ai. 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 Database for details
`SQL_TRANSLATION_PROFILE`
`SQL_TRANSPILER`	This parameter is only applicable with Oracle Database 23ai. 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 Database 23ai. 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.
`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
`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 Database 23ai. 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 Database.
`OPTIMIZER_IGNORE_PARALLEL_HINTS`	For more information on `OPTIMIZER_IGNORE_PARALLEL_HINTS`, see Manage Optimizer Statistics on Autonomous 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 Database 23ai. 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 Database 23ai. 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.

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.

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 Database 23ai 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