Oracle and PostgreSQL session parameters - Oracle to Aurora PostgreSQL Migration Playbook
Oracle usagePostgreSQL usageSummary
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Oracle and PostgreSQL session parameters

With Amazon DMS, you can configure session parameters for Oracle and PostgreSQL databases to optimize performance and customize behavior during migration tasks. Session parameters are special configuration options that influence how the database engine operates and processes data.

Feature compatibility Amazon SCT / Amazon DMS automation level Amazon SCT action code index Key differences

One star feature compatibility

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.