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.

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';

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';

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

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 

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

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.

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.

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.

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.

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

Values

• ENFORCED: Irrespective of the setting of RESULT_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 of RESULT_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 example SYSDATE or constructs involving SYSDATE).

See RESULT_CACHE_MODE for more information.