Help Center> Data Warehouse Service> SQL Syntax Reference> DDL Syntax> ALTER SESSION
View PDF

ALTER SESSION

Function

ALTER SESSION defines or modifies the conditions or parameters that affect the current session. Modified session parameters are kept until the current session is disconnected.

Precautions

  • If the START TRANSACTION command is not executed before the SET TRANSACTION command, the transaction is ended instantly and the command does not take effect.
  • You can use the transaction_mode(s) method declared in the START TRANSACTION command to avoid using the SET TRANSACTION command.

Syntax

  • Set transaction parameters of a session. 
    1
2
    		 
    ALTER SESSION SET [ SESSION CHARACTERISTICS AS ] TRANSACTION
    { ISOLATION LEVEL { READ COMMITTED | READ UNCOMMITTED } | { READ ONLY  | READ WRITE } } [, ...] ;
  • Set other running parameters of a session. 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
    		 
    ALTER SESSION SET 
    {{config_parameter { { TO  | =  }  { value | DEFAULT }
      | FROM CURRENT }} | CURRENT_SCHEMA [ TO | = ] { schema | DEFAULT }
      | TIME ZONE time_zone
      | SCHEMA schema
      | NAMES encoding_name
      | ROLE role_name PASSWORD 'password'
      | SESSION AUTHORIZATION { role_name PASSWORD 'password' | DEFAULT }
      | XML OPTION { DOCUMENT | CONTENT }
    } ;

Parameter Description

To modify the description of parameters related to the session, see Parameter Description of the SET syntax.

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
 
-- Create the ds schema:
CREATE SCHEMA ds;

-- Set the search path of the schema:
SET SEARCH_PATH TO ds, public;

-- Set the time/date type to the traditional postgres format (date before month):
SET DATESTYLE TO postgres, dmy;

-- Set the character code of the current session to UTF8:
ALTER SESSION SET NAMES 'UTF8';

-- Set the time zone to Berkeley of California:
SET TIME ZONE 'PST8PDT';

-- Set the time zone to Italy:
SET TIME ZONE 'Europe/Rome';

-- Set the current schema:
ALTER SESSION SET CURRENT_SCHEMA TO tpcds;

-- Set XML OPTION to DOCUMENT:
ALTER SESSION SET XML OPTION DOCUMENT;

-- Create the role joe, and set the session role to omm:
CREATE ROLE joe WITH PASSWORD 'Bigdata123@';
ALTER SESSION SET SESSION AUTHORIZATION joe PASSWORD 'Bigdata123@';

-- Switch to the default user:
ALTER SESSION SET SESSION AUTHORIZATION default;

-- Delete the ds schema:
DROP SCHEMA ds;

-- Delete the role joe:
DROP ROLE joe;

Helpful Links

SET

Parent topic: DDL Syntax