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