What is NLS_SESSION_PARAMETERS ?
NLS_SESSION_PARAMETERS is a dictionary view which shows all current NLS parameters of an user's session. Let's see their values.
SQL> set pagesize 1000;
SQL> column parameter format a30;
SQL> column value format a30;
SQL> select * from nls_session_parameters order by 1;
PARAMETER VALUE
------------------------------ ------------------------------
NLS_CALENDAR GREGORIAN
NLS_COMP BINARY
NLS_CURRENCY $
NLS_DATE_FORMAT YYYY-MM-DD HH24:MI:SS
NLS_DATE_LANGUAGE AMERICAN
NLS_DUAL_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_LANGUAGE AMERICAN
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NUMERIC_CHARACTERS .,
NLS_SORT BINARY
NLS_TERRITORY AMERICA
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
17 rows selected.
Their values mainly inherit from NLS_INSTANCE_PARAMETERS, the rest comes from NLS_DATABASE_PARAMETERS. Initial NLS values of a session can be overridden later.
How to Change NLS_SESSION_PARAMETERS ?
Only a part of NLS parameters can be changed at session-level, we should use ALTER SESSION SET to change them.
NLS_DATE_FORMAT
SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
Session altered.
There're more ways to set NLS_DATE_FORMAT.
NLS_LENGTH_SEMANTICS
SQL> alter session set nls_length_semantics=CHAR;
Session altered.
That's right, we can change NLS_LENGTH_SEMANTICS in our sessions, there's no need to set it at instance-level.
Let's check again.
SQL> select * from nls_session_parameters order by 1;
PARAMETER VALUE
------------------------------ ------------------------------
NLS_CALENDAR GREGORIAN
NLS_COMP BINARY
NLS_CURRENCY $
NLS_DATE_FORMAT YYYY-MM-DD HH24:MI:SS
NLS_DATE_LANGUAGE AMERICAN
NLS_DUAL_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_LANGUAGE AMERICAN
NLS_LENGTH_SEMANTICS CHAR
NLS_NCHAR_CONV_EXCP FALSE
NLS_NUMERIC_CHARACTERS .,
NLS_SORT BINARY
NLS_TERRITORY AMERICA
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
17 rows selected.
They have been changed.
As for the question, which value at various level will be used? the rule is that the values of NLS parameters at session-level take precedence over other level's values. We have a good example to talk about it: NLS_DATE_FORMAT, How and Why.
So far, we have learned: NLS_DATABASE_PARAMETERS, NLS_INSTANCE_PARAMETERS and NLS_SESSION_PARAMETERS.
Is there any way to set NLS parameters at runtime? Maybe, maybe not. Please check the following posts for sure.