Session Parameters

Feature compatibility	AWS SCT / AWS DMS automation level	AWS SCT action code index	Key differences
	N/A	N/A	SET options are significantly different in PostgreSQL.

Oracle Usage

Certain Oracle database parameters and configuration options are modifiable at the session level using the ALTER SESSION command. However, not all Oracle configuration options and parameters can be modified on a per-session basis. To view a list of all configurable parameters that can be set for the scope of a specific session, query the v$parameter view as shown in the following example.

SELECT NAME, VALUE FROM V$PARAMETER WHERE ISSES_MODIFIABLE='TRUE';

Examples

Change the NLS_LANAUGE codepage parameter of the current session.

alter session set nls_language='SPANISH'

Sesi≤n modificada.

alter session set nls_language='ENGLISH';

Session altered.

alter session set nls_language='FRENCH';

Session modifiΘe.

alter session set nls_language='GERMAN';

Session wurde geΣndert.

Specify the format of date values returned from the database using the NLS_DATE_FORMAT session parameter.

select sysdate from dual;

SYSDATE
SEP-09-17

alter session set nls_date_format='DD-MON-RR';

Session altered.

select sysdate from dual;

SYSDATE
09-SEP-17

alter session set nls_date_format='MM-DD-YYYY';

Session altered.

select sysdate from dual;

SYSDATE
09-09-2017

alter session set nls_date_format='DAY-MON-RR';

Session altered.

For more information, see Changing Parameter Values in a Parameter File in the Oracle documentation.

PostgreSQL Usage

PostgreSQL provides session-modifiable parameters that are configured using the SET SESSION command. Configuration of parameters using SET SESSION will only be applicable in the current session. To view the list of parameters that can be set with SET SESSION, you can query pg_settings.

SELECT * FROM pg_settings where context = 'user';

Find the commonly used session parameters following:

• client_encoding configures the connected client character set.

• force_parallel_mode forces use of parallel query for the session.

• lock_timeout sets the maximum allowed duration of time to wait for a database lock to release.

• search_path sets the schema search order for object names that are not schema-qualified.

• transaction_isolation sets the current Transaction Isolation Level for the session.

Examples

Change the Time zone of the connected session.

set session DateStyle to POSTGRES, DMY;
SET

select now();

now
Sat 09 Sep 11:03:43.597202 2017 UTC
(1 row)

set session DateStyle to ISO, MDY;
SET

select now();

now
2017-09-09 11:04:01.3859+00
(1 row)

Summary

The following table includes a partial list of parameters and is meant to highlight various session-level configuration parameters in both Oracle and PostgreSQL. Not all parameters are directly comparable.

Parameter purpose	Oracle	PostgreSQL
Configure time and date format	ALTER SESSION SET nls_date_format = 'dd/mm/yyyy hh24:mi:ss';	SET SESSION datestyle to 'SQL, DMY';
Configure the current default schema or database	ALTER SESSION SET current schema='schema_name'	SET SESSION SEARCH_PATH TO schemaname;
Generate traces for specific errors	ALTER SESSION SET events '10053 trace name context forever';	N/A
Run trace for a SQL statement	ALTER SESSION SET sql_trace=TRUE; ALTER SYSTEM SET EVENTS 'sql_trace [sql:&&sql_id] bindd=true, wait=true';	N/A
Modify query optimizer cost for index access	ALTER SESSION SET optimizer_index_cost_adj = 50	SET SESSION random_page_cost TO 6;
Modify query optimizer row access strategy	ALTER SESSION SET optimizer_mode=all_rows;	N/A
Memory allocated to sort operations	ALTER SESSION SET sort_area_size=6321;	SET SESSION work_mem TO '6MB';
Memory allocated to hash joins	ALTER SESSION SET hash_area_size=1048576000;	SET SESSION work_mem TO '6MB';

For more information, see SET in the PostgreSQL documentation.