关于nls_database_parameters、nls_instance_parameters、nls_session_parameters

今天，在网上搜索关于nls视图的相关内容，结果越查越糊涂，无奈下，还是自己动手做一下。

首先，我们看一下，这三个视图的sql定义：

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)
			  from v$nls_parameters
			  where parameter != 'NLS_CHARACTERSET' and
			   parameter != 'NLS_NCHAR_CHARACTERSET'

NLS_INSTANCE_PARAMETERS   select substr(upper(name), 1, 30),
				 substr(value, 1, 40)
			  from v$system_parameter
			  where name like 'nls%'


VIEW_NAME		  TEXT
------------------------- --------------------------------------------------
NLS_DATABASE_PARAMETERS   select name,
				 substr(value$, 1, 40)
			  from props$
			  where name like 'NLS%'

可以看出，nls_database_parameters取值与props$,即我们创建数据库时存储在数据库中的熟悉信息，这与环境变量和参数文件等是统统没有关系的。nls_instance_parameters取值与v$system_parameter,下面让我们看一下v$system_parameter 在官方文档的定义：

V$SYSTEM_PARAMETER displays information about the initialization parameters that are currently in effect for the instance. 

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

从这里，我们可知，nls_instance_parameters的值有参数文件决定，那么会不会受到环境变量的影响哪？经验证，是不会的！过程如下：

首先，我们来看一下NLS_LANGUAGE的取值

sys>select parameter,value from nls_instance_parameters where parameter='NLS_LANGUAGE';

PARAMETER		       VALUE
------------------------------ ------------------------------
NLS_LANGUAGE		       SIMPLIFIED CHINESE

关闭数据库，然后修改环境变量NLS_LANG:

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 Partitioning, 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 18 16:01:00 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an 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 bytes
Database mounted.
Database opened.
SQL> select parameter,value from nls_instance_parameters where parameter='NLS_LANGUAGE';

PARAMETER		       VALUE
------------------------------ ------------------------------
NLS_LANGUAGE		       SIMPLIFIED CHINESE

可见，nls_instance_parameters只受参数文件的影响，而不受环境变量影响。

nls_session_parameters取值与V$NLS_PARAMETERS,它的查询结果默认会从nls_instance_parameters继承，但是如果，我们在环境变量或者通过ALTER SESSION 改变了nls的相关参数，则会覆盖默认值。

例如，当环境变量NLS_LANG=AMERICAN_AMERICA.AL32UTF8

SQL> select parameter,value from nls_session_parameters where parameter='NLS_LANGUAGE';

PARAMETER		       VALUE
------------------------------ ------------------------------
NLS_LANGUAGE		       AMERICAN

当环境变量NLS_LANG='SIMPLIFIED CHINESE_CHINA.AL32UTF8'

SQL> select parameter,value from nls_session_parameters where parameter='NLS_LANGUAGE';

PARAMETER		       VALUE
------------------------------ ------------------------------
NLS_LANGUAGE		       SIMPLIFIED CHINESE