[Go] Discussion on the problem of Oracle character set (Nls_language)

Discussion on the problem of Oracle character set (Nls_language)
--------------------------------------------------------------------------------
Nls_database_parameters, Nls_instance_parameters, nls_session_parameters
--------------------------------------------------------------------------------

Today, search online about the NLS view related content, the result is more confused, helpless, or do it yourself.

First, let's take a look at the SQL definitions for these three views:

Sys> Select View_name,text from dba_views where view_name like ' nls% ';

View_name TEXT
------------------------- --------------------------------------------------
Nls_session_parameters Select substr (parameter, 1, 30),
substr (value, 1, 40)
Fromv$nls_parameters
WHERE parameter! = ' Nls_characterset ' and
Parameter! = ' Nls_nchar_characterset '

Nls_instance_parameters Select substr (Upper (name), 1, 30),
substr (value, 1, 40)
FromV$system_parameter
Where name like ' nls% '


View_name TEXT
------------------------- --------------------------------------------------
Nls_database_parameters select Name,
SUBSTR (value$, 1, 40)
Fromprops$
Where name like ' nls% '

Can be seen:

Nls_database_parameters is the value of props$, which is the information stored in the database when we create the database, which is not related to environment variables and parameter files.

nls_instance_parameters value in V$system_parameter, let's take a look at the definition of V$system_parameter in the official document:

V$system_parameter displays information about the initialization parameters that is currently in effect for the instance.

A new session inherits parameter values from the Instance-wide values.

From here, we know that the value of nls_instance_parameters is determined by the parameter file, then it will not be affected by environment variables?

Proven , no! the process is as follows:

First, let's take a look at the value of Nls_language.

Sys>select Parameter,value from Nls_instance_parameters where parameter= ' nls_language ';

PARAMETER VALUE
------------------------------ ------------------------------
Nls_language Simplified Chinese

Close the database, and then modify the environment variable Nls_lang:

Sys>shutdown Immediate

Sys>exit

[Email protected] ~]$ Vi. bash_profile
[Email protected] ~]$. . bash_profile
[[Email protected] ~]$ Set | grep NLS
nls_date_format= ' Yyyy-mm-dd HH24:MI:SS '
Nls_lang=american_america. Al32utf8

[Email protected] ~]$ Sqlplus/as SYSDBA

Sql> Startup

Sql> Select Parameter,value from nls_instance_parameters where parameter= ' nls_language ';

PARAMETER VALUE
------------------------------ ------------------------------
Nls_language Simplified Chinese

as can be seen, nls_instance_parameters is only affected by parameter files, not by environment variables.

The Nls_session_parameters value is v$nls_parameters, and its query results are inherited from Nls_instance_parameters by default, but if we are in an environment variable or

The parameters of the NLS are changed by Alter SESSION, and the default values are overwritten.

For example, when the environment variable Nls_lang=american_america. Al32utf8

Sql> Select Parameter,value from nls_session_parameters where parameter= ' nls_language ';

PARAMETER VALUE
------------------------------ ------------------------------
Nls_language AMERICAN

When the environment variable nls_lang= ' simplified Chinese_china. Al32utf8 '

Sql> Select Parameter,value from nls_session_parameters where parameter= ' nls_language ';

PARAMETER VALUE
------------------------------ ------------------------------
Nls_language Simplified Chinese

Source: csdn Easy Point