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.
Modifiable Initialization Parameters
The following table shows the initialization parameters that are only
modifiable with ALTER SESSION
.
Only Modifiable with ALTER SESSION | Notes |
---|---|
CONSTRAINTS |
|
CONTAINER |
|
CURRENT_SCHEMA |
|
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. |
READ_ONLY |
|
SQL_TRACE |
See Perform SQL Tracing on Autonomous Database for details |
|
|
SQL_TRANSPILER |
This parameter is only applicable with Oracle Database 23ai. See SQL_TRANSPILER for more information. |
STATISTICS_LEVEL |
|
TIME_ZONE |
For more information on |
The following table shows the initialization parameters that are only
modifiable with ALTER SYSTEM
.
Only Modifiable with ALTER SYSTEM | Notes |
---|---|
BLANK_TRIMMING |
|
FIXED_DATE |
|
JOB_QUEUE_PROCESSES |
|
LOCKDOWN_ERRORS |
See LOCKDOWN_ERRORS for details |
MAX_IDLE_BLOCKER_TIME |
With a value higher than 5, the parameter acts as if it was set to 5 |
MAX_IDLE_TIME |
|
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 | Notes |
---|---|
APPROX_FOR_AGGREGATION |
|
APPROX_FOR_COUNT_DISTINCT |
|
APPROX_FOR_PERCENTILE |
|
CLIENT_PREFETCH_ROWS |
|
CONTAINER_DATA |
|
CURSOR_SHARING |
|
DDL_LOCK_TIMEOUT |
|
GROUP_BY_POSITION_ENABLED |
This parameter is only applicable with Oracle Database 23ai. See GROUP_BY_POSITION_ENABLED for more information |
IGNORE_SESSION_SET_PARAM_ERRORS |
|
LDAP_DIRECTORY_ACCESS |
|
LOAD_WITHOUT_COMPILE |
|
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
|
For more information on |
OPTIMIZER_IGNORE_PARALLEL_HINTS |
For more information on
|
OPTIMIZER_MODE |
|
OPTIMIZER_REAL_TIME_STATISTICS
|
|
OPTIMIZER_USE_SQL_QUARANTINE |
|
PLSCOPE_SETTINGS |
|
PLSQL_CCFLAGS |
|
PLSQL_DEBUG |
|
PLSQL_OPTIMIZE_LEVEL |
|
PLSQL_WARNINGS |
|
QUERY_REWRITE_INTEGRITY |
|
RECYCLE_BIN
|
|
REMOTE_DEPENDENCIES_MODE |
|
RESULT_CACHE_INTEGRITY |
|
RESULT_CACHE_MODE |
|
SKIP_UNUSABLE_INEDEXES |
|
SYSDATE_AT_DBTIMEZONE |
See SYSDATE_AT_DBTIMEZONE Select a Time Zone for SYSDATE on Autonomous Database |
For more information on initialization parameters see Oracle 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. - SYSDATE_AT_DBTIMEZONE Select a Time Zone for SYSDATE on Autonomous Database
SYSDATE_AT_DBTIMEZONE
enables special handling in a session for the date and time value returned in calls toSYSDATE
andSYSTIMESTAMP
. - CLIENT_PREFETCH_ROWS
Set theCLIENT_PREFETCH_ROWS
parameter to enable clients to reduce the number of roundtrips required while fetching rows of a query result set. - JOB_QUEUE_PROCESSES
Set theJOB_QUEUE_PROCESSES
parameter to specify the maximum number of job workers that can be created to run Oracle Scheduler (DBMS_SCHEDULER
) jobs. - LOCKDOWN_ERRORS
Set theLOCKDOWN_ERRORS
parameter to control whether Autonomous Database lockdown profile errors are raised or ignored. - RESULT_CACHE_INTEGRITY
Set theRESULT_CACHE_INTEGRITY
parameter to specify whether the result cache considers queries using possibly non-deterministic constructs as candidates for result caching. - RESULT_CACHE_MODE
Set theRESULT_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.
Parent topic: Notes for Users Migrating from Other Oracle Databases
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.
Parent topic: Initialization Parameters
SYSDATE_AT_DBTIMEZONE Select a Time Zone for SYSDATE on Autonomous 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 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';
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.
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
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
Parent topic: Initialization Parameters
CLIENT_PREFETCH_ROWS
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 defaultOCI_ATTR_PREFETCH_ROWS
value for prefetch row count. -
If the
OCI_ATTR_PREFETCH_ROWS
value is set to a non-default value, then theCLIENT_PREFETCH_ROWS
value is ignored for the prefetch row count. -
Using
CLIENT_PREFETCH_ROWS
withOCIAttrSet()
:OCI_ATTR_PREFETCH_ROWS
sets the number of top-level rows to be prefetched. The default value is 1 row. However, ifCLIENT_PREFETCH_ROWS
is set, the number of top-level rows to be prefetched is determined by the following precedence:-
If you set the
OCI_ATTR_PREFETCH_ROWS
attribute usingOCIAttrSet()
function ororaaccess.xml
as the value '1', then the database initialization parameterCLIENT_PREFETCH_ROWS
value takes precedence and determines the number of top-level rows to be prefetched. -
If you set the
OCI_ATTR_PREFETCH_ROWS
attribute usingOCIAttrSet()
function ororaaccess.xml
as the value 'x' other than 1, then 'x' number of top-level rows will be prefetched, and the database initialization parameterCLIENT_PREFETCH_ROWS
is ignored. -
If you do not set an
OCI_ATTR_PREFETCH_ROWS
value usingOCIAttrSet()
ororaaccess.xml
, then the database initialization parameterCLIENT_PREFETCH_ROWS
value takes precedence and determines the number of top-level rows to be prefetched.
-
-
Using
CLIENT_PREFETCH_ROWS
withOCIAttrGet()
:The function
OCIAttrGet()
returns the effective prefetch row value set fromOCI_ATTR_PREFETCH_ROWS
,oraaccess.xml
and the database initialization parameterCLIENT_PREFETCH_ROWS
. If theOCI_ATTR_PREFETCH_MEMORY
value is set, the value returned byOCIAttrGet()
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 theOCI_ATTR_PREFETCH_MEMORY
attribute.
See Also:
-
Oracle Call Interface Developer's Guide for more information about fetching results and setting the prefetch count.
Parent topic: Initialization Parameters
JOB_QUEUE_PROCESSES
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: 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 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.
Parent topic: Initialization Parameters
LOCKDOWN_ERRORS
Set the
LOCKDOWN_ERRORS
parameter to control whether Autonomous Database lockdown profile errors are
raised or ignored.
Autonomous 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 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.
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 |
Modifiable | ALTER SYSTEM |
Valid values |
|
Parent topic: Initialization Parameters
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
Database the default
value is: |
Modifiable | ALTER SYSTEM |
Values
-
ENFORCED
: Irrespective of the setting ofRESULT_CACHE_MODE
or specified hints, only deterministic constructs are eligible for result caching. For example, queries using PL/SQL functions that are not declared as deterministic are never cached (unless the functions are declared as deterministic the query results with such functions will not be cached). -
TRUSTED
: The database honors the setting ofRESULT_CACHE_MODE
and specified hints and will consider queries using possibly non-deterministic constructs as candidates for result caching. For example, queries using PL/SQL functions that are not declared as deterministic can be cached. Note, however, that results that are known to be non-deterministic are not cached (for exampleSYSDATE
or constructs involvingSYSDATE
).
Parent topic: Initialization Parameters
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: For workload types Transaction Processing, JSON, or APEX: |
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.
Parent topic: Initialization Parameters
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 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.
Parent topic: RESULT_CACHE_MODE