What is NLS_INSTANCE_PARAMETERS ?
NLS_INSTANCE_PARAMETERS is a dictionary view which shows default NLS parameters of an instance. Let's see their values.
SQL> set pagesize 1000;
SQL> column parameter format a30;
SQL> column value format a30;
SQL> select * from nls_instance_parameters order by 1;
PARAMETER VALUE
------------------------------ ------------------------------
NLS_CALENDAR
NLS_COMP BINARY
NLS_CURRENCY
NLS_DATE_FORMAT
NLS_DATE_LANGUAGE
NLS_DUAL_CURRENCY
NLS_ISO_CURRENCY
NLS_LANGUAGE AMERICAN
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NUMERIC_CHARACTERS
NLS_SORT
NLS_TERRITORY AMERICA
NLS_TIMESTAMP_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
NLS_TIME_FORMAT
NLS_TIME_TZ_FORMAT
17 rows selected.
Their values inherit from NLS_DATABASE_PARAMETERS initially.
How to Change NLS_INSTANCE_PARAMETERS ?
Only a part of NLS parameters can be changed at instance-level, we should use ALTER SYSTEM SET to change them.
SQL> alter system set NLS_TERRITORY=SWEDEN scope=spfile sid='*';
System altered.
SQL> alter system set NLS_LANGUAGE=SWEDISH scope=spfile sid='*';
System altered.
As you can see, we changed NLS_TERRITORY and NLS_LANGUAGE in this case.
After a restart, let's check the current NLS instance parameters.
SQL> select * from nls_instance_parameters order by 1;
PARAMETER VALUE
------------------------------ ------------------------------
NLS_CALENDAR
NLS_COMP BINARY
NLS_CURRENCY
NLS_DATE_FORMAT
NLS_DATE_LANGUAGE
NLS_DUAL_CURRENCY
NLS_ISO_CURRENCY
NLS_LANGUAGE SWEDISH
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NUMERIC_CHARACTERS
NLS_SORT
NLS_TERRITORY SWEDEN
NLS_TIME_FORMAT
NLS_TIMESTAMP_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
NLS_TIME_TZ_FORMAT
17 rader valda.
To know the relationship between NLS_TERRITORY and NLS_LANGUAGE, there's a mapping table, it might be helpful to solve globalization problem.