Categories:

Account & Session DDL

ALTER SESSION

Sets parameters that change the behavior for the current session.

See also:

SHOW PARAMETERS

Syntax

ALTER SESSION SET sessionParams

ALTER SESSION UNSET <param_name> [ , <param_name> , ... ]

Where:

sessionParams ::=
  ABORT_DETACHED_QUERY = TRUE | FALSE
  AUTOCOMMIT = TRUE | FALSE
  DATE_INPUT_FORMAT = <string>
  DATE_OUTPUT_FORMAT = <string>
  ERROR_ON_NONDETERMINISTIC_MERGE = TRUE | FALSE
  ERROR_ON_NONDETERMINISTIC_UPDATE = TRUE | FALSE
  LOCK_TIMEOUT = <num>
  QUERY_TAG = <string>
  ROWS_PER_RESULTSET = <num>
  STATEMENT_TIMEOUT_IN_SECONDS = <num>
  TIMESTAMP_DAY_IS_ALWAYS_24H = TRUE | FALSE
  TIMESTAMP_INPUT_FORMAT = <string>
  TIMESTAMP_LTZ_OUTPUT_FORMAT = <string>
  TIMESTAMP_NTZ_OUTPUT_FORMAT = <string>
  TIMESTAMP_OUTPUT_FORMAT = <string>
  TIMESTAMP_TYPE_MAPPING = <string>
  TIMESTAMP_TZ_OUTPUT_FORMAT = <string>
  TIMEZONE = <string>
  TIME_INPUT_FORMAT = <string>
  TIME_OUTPUT_FORMAT = <string>
  TRANSACTION_DEFAULT_ISOLATION_LEVEL = <string>
  TWO_DIGIT_CENTURY_START = <num>
  UNSUPPORTED_DDL_ACTION = <string>
  USE_CACHED_RESULT = TRUE | FALSE

Parameters

SET ...

Specifies one (or more) parameters to set for the session (separated by blank spaces, commas, or new lines).

For descriptions of each of the parameters you can set for a session, see Parameters.

UNSET ...

Specifies one (or more) parameters to unset for the session, which resets them to the defaults.

You can reset multiple parameters with a single ALTER statement; however, each property must be separated by a comma. When resetting a property, specify only the name; specifying a value for the property will return an error.

Usage Notes

  • Parameters are typed. The supported types are BOOLEAN, NUMBER, and STRING.

  • To see the current parameter values for the session, use SHOW PARAMETERS.

Examples

Set the lock timeout for statements executed in the session to 1 hour (3600 seconds):

ALTER SESSION SET LOCK_TIMEOUT = 3600;

Set the lock timeout for statements executed in the session back to the default:

ALTER SESSION UNSET LOCK_TIMEOUT;