Initialization Parameters

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

List of Initialization Parameters that can be Modified

APPROX_FOR_AGGREGATION
APPROX_FOR_COUNT_DISTINCT
APPROX_FOR_PERCENTILE
BLANK_TRIMMING (Allowed only with ALTER SYSTEM)
CLIENT_PREFETCH_ROWS (see CLIENT_PREFETCH_ROWS
CONSTRAINTS (Allowed only with ALTER SESSION)
CONTAINER (Allowed only with ALTER SESSION)
CONTAINER_DATA
CURRENT_SCHEMA (Allowed only with ALTER SESSION)
CURSOR_INVALIDATION (Allowed only with ALTER SESSION)
CURSOR_SHARING
DDL_LOCK_TIMEOUT
DEFAULT_COLLATION (Allowed only with ALTER SESSION)
DEFAULT_CREDENTIAL (Allowed only with ALTER SESSION)
EDITION (Allowed only with ALTER SESSION)
FIXED_DATE (Allowed only with ALTER SYSTEM)
IGNORE_SESSION_SET_PARAM_ERRORS
ISOLATION_LEVEL (Allowed only with ALTER SESSION)
LDAP_DIRECTORY_ACCESS
LOAD_WITHOUT_COMPILE 
MAX_IDLE_TIME (Allowed only with ALTER SYSTEM)
MAX_STRING_SIZE (See Data Types for details)
NLS_CALENDAR
NLS_COMP
NLS_CURRENCY
NLS_DATE_FORMAT
NLS_DATE_LANGUAGE
NLS_DUAL_CURRENCY
NLS_ISO_CURRENCY
NLS_LANGUAGE
NLS_LENGTH_SEMANTICS
NLS_NCHAR_CONV_EXCP
NLS_NUMERIC_CHARACTERS
NLS_SORT
NLS_TERRITORY
NLS_TIME_FORMAT
NLS_TIME_TZ_FORMAT
NLS_TIMESTAMP_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
OPTIMIZER_CAPTURE_SQL_QUARANTINE
OPTIMIZER_IGNORE_HINTS
OPTIMIZER_IGNORE_PARALLEL_HINTS
OPTIMIZER_MODE
OPTIMIZER_REAL_TIME_STATISTICS
OPTIMIZER_USE_SQL_QUARANTINE
PLSCOPE_SETTINGS
PLSQL_CCFLAGS
PLSQL_DEBUG
PLSQL_OPTIMIZE_LEVEL
PLSQL_WARNINGS
QUERY_REWRITE_INTEGRITY
READ_ONLY (Allowed only with ALTER SESSION)
RECYCLE_BIN 
REMOTE_DEPENDENCIES_MODE
RESULT_CACHE_MODE (See RESULT_CACHE_MODE)
SESSION_EXIT_ON_PACKAGE_STATE_ERROR (Allowed only with ALTER SYSTEM) See SESSION_EXIT_ON_PACKAGE_STATE_ERROR
SKIP_UNUSABLE_INEDEXES
SQL_TRACE (Allowed only with ALTER SESSION) See Perform SQL Tracing on Autonomous Database for details
SQL_TRANSLATION_PROFILE (Allowed only with ALTER SESSION)
STATISTICS_LEVEL (Allowed only with ALTER SESSION)
SYSDATE_AT_DBTIMEZONE (See SYSDATE_AT_DBTIMEZONE)
TIMEZONE (Allowed only with ALTER SESSION)

For more information on initialization parameters see Oracle Database Reference. For more information on TIME_ZONE, see Oracle Database SQL Language Reference.

For more information on OPTIMIZER_IGNORE_HINTS and OPTIMIZER_IGNORE_PARALLEL_HINTS, see Manage Optimizer Statistics on Autonomous Database.

• 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.
• SYSDATE_AT_DBTIMEZONE
  SYSDATE_AT_DBTIMEZONE enables special handling in a session for the date and time value returned in calls to SYSDATE and SYSTIMESTAMP.
• 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.
• 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.

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

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 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 Database Time Zone

The default Autonomous 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';

Note:

You must restart the Autonomous 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 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 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

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:

• When CLIENT_PREFETCH_ROWS is set to a non-zero value, its value takes precedence over the default OCI_ATTR_PREFETCH_ROWS value for prefetch row count.

• If the OCI_ATTR_PREFETCH_ROWS value is set to a non-default value, then the CLIENT_PREFETCH_ROWS value is ignored for the prefetch row count.

• Using CLIENT_PREFETCH_ROWS with OCIAttrSet():

  OCI_ATTR_PREFETCH_ROWS sets the number of top-level rows to be prefetched. The default value is 1 row. However, if CLIENT_PREFETCH_ROWS is set, the number of top-level rows to be prefetched is determined by the following precedence:

  1. If you set the OCI_ATTR_PREFETCH_ROWS attribute using OCIAttrSet() function or oraaccess.xml as the value '1', then the database initialization parameter CLIENT_PREFETCH_ROWS value takes precedence and determines the number of top-level rows to be prefetched.

  2. If you set the OCI_ATTR_PREFETCH_ROWS attribute using OCIAttrSet() function or oraaccess.xml as the value 'x' other than 1, then 'x' number of top-level rows will be prefetched, and the database initialization parameter CLIENT_PREFETCH_ROWS is ignored.

  3. If you do not set an OCI_ATTR_PREFETCH_ROWS value using OCIAttrSet() or oraaccess.xml, then the database initialization parameter CLIENT_PREFETCH_ROWS value takes precedence and determines the number of top-level rows to be prefetched.

• Using CLIENT_PREFETCH_ROWS with OCIAttrGet():

  The function OCIAttrGet() returns the effective prefetch row value set from OCI_ATTR_PREFETCH_ROWS, oraaccess.xml and the database initialization parameter CLIENT_PREFETCH_ROWS. If the OCI_ATTR_PREFETCH_MEMORY value is set, the value returned by OCIAttrGet() might not be the final prefetch rows value and may be restricted to the maximum number of rows allowed by the memory value specified by the OCI_ATTR_PREFETCH_MEMORY attribute.

See Also:

• Oracle Call Interface Developer's Guide for more information about fetching results and setting the prefetch count.

• OCIAttrSet()

• OCI_ATTR_PREFETCH_ROWS

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 Database with workload type set to Data Warehouse: 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.

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

• Using the NO_RESULT_CACHE Hint

• Using the RESULT_CACHE Hint in Views

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 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 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 for more information.