ALTER SESSION

ALTER SESSION sets and clears session-level configuration parameter values for the current 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] parameter-name[,...] | PARAMETER ALL }
    | 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)

Clear all session-level configuration parameters of changes that were set in this session:
```
=> ALTER SESSION CLEAR PARAMETER ALL;
ALTER SESSION
```

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