ALTER SESSION

ALTER SESSION sets and clears session-level configuration parameter values for the current session. To identify session-level parameters, query system table CONFIGURATION_PARAMETERS.

Syntax

ALTER SESSION {
    SET [PARAMETER] parameter‑name=value[,…]
    | CLEAR [PARAMETER [FOR]] parameter‑name[,…]
    | SET UDPARAMETER [ FOR libname ] key=value[,…]
    | CLEAR UDPARAMETER { [ FOR libname ] key[,…] | ALL }
}

Parameters

SET [PARAMETER] Sets one or more configuration parameters to the specified value.
CLEAR [PARAMETER] Clears the specified configuration parameters of changes that were set in the current session.
CLEAR PARAMETER ALL Clears all session-level configuration parameters of changes that were set in the current session.
SET UDPARAMETER

Sets one or more user-defined session parameters (key=value) to be used with a UDx. Key value sizes are restricted as follows:

  • Set from client side: 128 characters
  • Set from UDx side: unlimited

You can limit the SET operation's scope to a single library by including the clause FOR libname. For example:

=> ALTER SESSION SET UDPARAMETER FOR securelib username='alice';

If you specify a library, then only that library can access the parameter's value. Use this restriction to protect parameters that hold sensitive data, such as credentials.

CLEAR UDPARAMETER

Clears user-defined parameters, specified by one of the following options:

  • [FOR libname] key[,…]: Clears the key-specified parameters, optionally scoped to library libname.
  • ALL: Clears all user-defined parameters in the current session.

Privileges

None

Examples

Set and clear a parameter

  • Force all UDxes that support fenced mode to run in fenced mode, even if their definition specifies NOT FENCED:

    => ALTER SESSION SET ForceUDxFencedMode = 1;
    ALTER SESSION
  • Clear ForceUDxFencedMode at the session level. Its value is reset to its default value 0:

    => ALTER SESSION CLEAR ForceUDxFencedMode;
    ALTER SESSION
    => SELECT parameter_name, current_value, default_value FROM configuration_parameters WHERE  parameter_name = 'ForceUDxFencedMode';
       parameter_name   | current_value | default_value
    --------------------+---------------+---------------
     ForceUDxFencedMode | 0             | 0
    (1 row)
    

Set and clear a user-defined parameter

  • Set the value of user-defined parameter RowCount in library MyLibrary to 25.

    => ALTER SESSION SET UDPARAMETER FOR MyLibrary RowCount = 25;
    ALTER SESSION
  • Clear RowCount at the session level:

    => ALTER SESSION CLEAR UDPARAMETER FOR MyLibrary RowCount;
    ALTER SESSION