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) JOB_QUEUE_PROCESSES (Allowed only with ALTER SYSTEM) 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_INTEGRITY (See RESULT_CACHE_INTEGRITY) 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 Select a Time Zone for SYSDATE on Autonomous Database) 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 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
.
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';
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:
SettingSYSDATE_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
Parent topic: Initialization Parameters