ALTER SESSION

Purpose

Use this statement to configure the current user session.

Prerequisite

None

Syntax

alter_session::=

Alter session statement

Usage Notes

  • The session-based parameters are initialized with the system-wide parameters (for more information, see ALTER SYSTEM). However, you can overwrite it with the ALTER SESSION statement. The current settings are available in the EXA_PARAMETERS system table.
  • The moment a user logs out, changes to the settings made through ALTER SESSION are lost.
  • The following table describes the parameters you can set:
Parameters Description
TIME_ZONE

Defines the time zone in which the values of type TIMESTAMP WITH LOCAL TIME ZONE are interpreted. For more information, refer to the Date/Time Data Types section. The list of supported timezones is available in the system table EXA_TIME_ZONES. The function SESSIONTIMEZONE returns the current session time zone.

TIME_ZONE_BEHAVIOR

Defines the course of action for ambiguous and invalid timestamps within a certain time zone. For more information, refer to the Date/Time Data Types section.

TIMESTAMP_ARITHMETIC_BEHAVIOR

Defines the behavior for +/- operators:

  • INTERVAL: The difference of two datetime values is an interval. When adding a decimal value to a timestamp, the number is rounded to an integer and a full day is added.
  • DOUBLE: The difference of two datetime values is a double. When adding a decimal value to a timestamp, the fraction of days is added (hours, minutes, ...).

The result of subtracting two DATE values, which always results in an integer, is not changed.

NLS_DATE_FORMAT

Sets the date format used for conversions between dates and strings. For information on the possible formats, refer to the Date/Time Format Models section.

NLS_TIMESTAMP_FORMAT

Sets the timestamp format used for conversions between timestamps and strings. For information on the possible formats, refer to the Date/Time Format Models section.

NLS_DATE_LANGUAGE

Sets the language of the date format used in abbreviated month and day formats and those written in full. Possible languages are English (ENG = Default) and German (DEU). The English language can be set using ENG or ENGLISH and the German language with DEU, DEUTSCH, and GERMAN.

For more information about date/time formats, refer to the Date/Time Format Models section.

NLS_FIRST_DAY_OF_WEEK

Defines the first day of a week (integer 1-7 for Monday-Sunday).

NLS_NUMERIC_CHARACTERS

Defines the decimal and group characters used for representing numbers. This parameter is also relevant to the use of numeric format models (For more details, see Numeric Format Models).

DEFAULT_LIKE_ESCAPE_CHARACTER

Defines the escape character for the LIKE predicate (for more details, see Predicates) if it is not specified explicitly.

QUERY_CACHE

The parameter QUERY_CACHE defines the usage of a read cache for SELECT queries. If the syntactically identical query is sent multiple times (except upper/lower case, spaces, ...), then the database can read the result directly out of a cache instead of executing the query. This is only applicable if the corresponding schema objects haven't changed in the meantime.

The following values can be set:

  • ON: The query cache is used. Each query result that is expensive enough (defined internally by resource usage) is read from and written into the cache.
  • OFF: The query cache is not used.
  • READONLY: Results are read from the cache, however, additional new queries will not be cached.

Whether a query was returned from the cache can be determined by the column EXECUTION_MODE in the corresponding system tables (EXA_SQL_LAST_DAY).

QUERY_TIMEOUT

Defines how many seconds a statement may run before it is automatically aborted. When this point is reached, the statement may finish with an exception within a few seconds through an internal cancellation point. If this fails (for example because there are no such cancellation points or the query is slowed down due to disk operations) the query is terminated forcefully and the transaction is rolled back (including an internal reconnect).

Time spent waiting for other transactions (in state Waiting for session) is included. In case of EXECUTE SCRIPT the QUERY_TIMEOUT is applied to the script as a whole, and when reaching the timeout the script is terminated (including any statements being executed by the script).

Any changes of the QUERY_TIMEOUT within a script will only be applied when the script exits. The default value for QUERY_TIMEOUT is '0' (no restrictions).

CONSTRAINT_STATE_DEFAULT

This parameter defines the default state of constraints ('ENABLE' or 'DISABLE') in case the state wasn't explicitly specified during the creation (see also CREATE TABLE and ALTER TABLE (constraints)).

PROFILE

Activates or deactivates the profiling (values 'ON' or 'OFF'). For more details, see Profiling.

SCRIPT_LANGUAGES

Defines the script language aliases. For more details, see Adding New Packages to Existing Script Languages.

SQL_PREPROCESSOR_SCRIPT

Defines a preprocessor script. If the script is specified (a regular script which was created through CREATE SCRIPT), then every executed SQL statement is preprocessed by that script. For more information on SQL preprocessing, see SQL Preprocessor. For details about the script language, see Scripting.

Appropriate user privileges must exist for executing this script. You can deactivate the preprocessing by specifying the empty string '' or NULL.

PASSWORD_SECURITY_POLICY

Specifies the security rules for user passwords. For details for all the possible parameters, see Database Users and Roles, and Privileges.

This parameter can only be changed throughout the system using ALTER SYSTEM, but not for single sessions. However, the system-wide value is shown in the session parameter column.

PASSWORD_EXPIRY_POLICY

Specifies the expiry rules for user passwords. These system values can be overwritten for single users through ALTER USER statement. For more details, see Database Users and Roles, and Privileges.

This parameter can only be changed throughout the system using ALTER SYSTEM, but not for single sessions. However, the system-wide value is shown in the session parameter column.

NICE

If the parameter NICE is set to 'ON', the session's priority will be reduced. The resource manager then divides the weight of the user by the number of currently active sessions. Therefore, the session conserves resources, even across consumer groups. For more details about priorities, see Priorities.

DEFAULT_PRIORITY_GROUP

Specifies the default priority groups for users and roles. For more details about priorities, refer to Priorities topic.

This parameter can only be changed throughout the system using ALTER SYSTEM, but not for single sessions. However, the system-wide value is shown in the session parameter column.

SCRIPT_OUTPUT_ADDRESS

Defines the hostname (or IP address) and the port number of a server to which the standard output (for example, by Python's print function) of UDF scripts are forwarded. You can deactivate the redirection of the script output by setting this parameter to the empty string. For more information, refer to Debug UDF Script Output topic.

Examples

ALTER SESSION SET TIME_ZONE='EUROPE/BERLIN';
ALTER SESSION SET QUERY_TIMEOUT=120;
ALTER SESSION SET NLS_DATE_FORMAT='DDD-YYYY';
ALTER SESSION SET NLS_DATE_LANGUAGE='ENG';
SELECT TO_CHAR(TO_DATE('365-2007'),'DAY-DD-MONTH-YYYY') TO_CHAR1;
ALTER SESSION SET NLS_NUMERIC_CHARACTERS=',.';
SELECT TO_CHAR(123123123.45, '999G999G999D99') TO_CHAR2;
ALTER SESSION SET SCRIPT_OUTPUT_ADDRESS='10.242.2.21:3000'
ALTER SESSION SET SESSION_TEMP_DB_RAM_LIMIT = '10240M';