Knowledge about the NLS view

Today, on the Internet to search about the NLS view of the relevant content, the results of the 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),  
                 substr (value, 1,) from  
              v$nls_parameters  
              where Parameter!= ' Nls_characterset ' and  
               parameter!= ' nls_nchar_characterset   ' nls_instance_parameters Select SUBSTR (Upper (name), 1),  
                 substr (value, 1,) from  
              v$system_parameter  
              where name like ' nls% ' C15/>view_name         TEXT  
---------------------------------------------------------------------------  
nls_database_parameters   Select name,  
                 substr (value$, 1) from  
              props$  
              where name like ' NLS ' %'

As you can see, nls_database_parameters values and props$, the familiar information stored in the database when we create the database, have nothing to do with environment variables and parameter files. Nls_instance_parameters value and 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 the are in currently for the effect.

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

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

From here, we know that the value of the nls_instance_parameters is determined by the parameter file, then will not be affected by the environment variables? After verification, is not! 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:

The 
 sys>shutdown immediate Database closed.  
Database dismounted.  
ORACLE instance shut down. Sys>exit disconnected from Oracle Database 11g Enterprise Edition release 11.2.0.3.0-64bit Production with the Pa Rtitioning, OLAP, Data Mining and real application testing options [Oracle@oadata ~]$ vi. Bash_profile [oracle@oadata ~]$ . . bash_profile [Oracle@oadata ~]$ Set | grep NLS nls_date_format= ' yyyy-mm-dd HH24:MI:SS ' Nls_lang=american_america. Al32utf8 

[Oracle@oadata ~]$ sqlplus/as sysdba  
      
sql*plus:release 11.2.0.3.0 Production on Wed Sep 16:01:00 2013 Copyrig  
      
HT (c) 1982, Oracle.  All rights reserved.  
      
Connected to a idle instance.  
      
Sql> Startup  
ORACLE instance started.  
      
Total System Global area 2137886720 bytes  
Fixed Size          2230072 bytes  
Variable Size        1342179528 bytes  
database buffers      788529152 bytes  
Redo buffers 4947968  
database bytes.  
Database opened.  
Sql> Select Parameter,value from nls_instance_parameters where parameter= ' nls_language ';  
      
PARAMETER              VALUE  
------------------------------------------------------------  
nls_language               Simplified Chinese

Visible, Nls_instance_parameters is only affected by the parameter file, not by the environment variable.

Nls_session_parameters values and v$nls_parameters, its query results are inherited from Nls_instance_parameters by default, but if we are in the environment variable or through ALTER session The default values are overwritten if the relevant parameters of the NLS are changed.

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