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的取值
1
2
3
4
5
<code>sys></code><code>select</code> <code>parameter,value</code><code>from</code> <code>nls_instance_parameters</code><code>where</code> <code>parameter=</code><code>'NLS_LANGUAGE'</code><code>; </code>
<code> </code>
<code>PARAMETER VALUE </code>
<code>------------------------------ ------------------------------ </code>
<code>NLS_LANGUAGE SIMPLIFIED CHINESE</code>
关闭数据库,然后修改环境变量NLS_LANG:
6
7
8
9
10
11
12
<code>sys>shutdown immediate </code>
<code>Database</code> <code>closed. </code>
<code>Database</code> <code>dismounted. </code>
<code>ORACLE instance shut down. </code>
<code>sys>exit </code>
<code>Disconnected</code><code>from</code> <code>Oracle</code><code>Database</code> <code>11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production </code>
<code>With</code> <code>the Partitioning, OLAP, Data Mining</code><code>and</code> <code>Real</code> <code>Application Testing options </code>
<code>[oracle@oadata ~]$ vi .bash_profile </code>
<code>[oracle@oadata ~]$ . .bash_profile </code>
<code>[oracle@oadata ~]$</code><code>set</code> <code>| grep NLS </code>
<code>NLS_DATE_FORMAT=</code><code>'YYYY-MM-DD HH24:MI:SS'</code>
<code>NLS_LANG=AMERICAN_AMERICA.AL32UTF8</code>
13
14
15
16
17
18
19
20
21
22
23
<code>[oracle@oadata ~]$ sqlplus /</code><code>as</code> <code>sysdba </code>
<code>SQL*Plus: Release 11.2.0.3.0 Production</code><code>on</code> <code>Wed Sep 18 16:01:00 2013 </code>
<code>Copyright (c) 1982, 2011, Oracle. </code><code>All</code> <code>rights reserved. </code>
<code>Connected</code><code>to</code> <code>an idle instance. </code>
<code>SQL> startup </code>
<code>ORACLE instance started. </code>
<code>Total System</code><code>Global</code> <code>Area 2137886720 bytes </code>
<code>Fixed</code><code>Size</code> <code>2230072 bytes </code>
<code>Variable</code><code>Size</code> <code>1342179528 bytes </code>
<code>Database</code> <code>Buffers 788529152 bytes </code>
<code>Redo Buffers 4947968 bytes </code>
<code>Database</code> <code>mounted. </code>
<code>Database</code> <code>opened. </code>
<code>SQL></code><code>select</code> <code>parameter,value</code><code>from</code> <code>nls_instance_parameters</code><code>where</code> <code>parameter=</code><code>'NLS_LANGUAGE'</code><code>; </code>
可见,nls_instance_parameters只受参数文件的影响,而不受环境变量影响。
nls_session_parameters 它的查询结果默认会从nls_instance_parameters继承,但是如果,我们在环境变量或者通过ALTER SESSION 改变了nls的相关参数,则会覆盖默认值。
例如,当环境变量NLS_LANG=AMERICAN_AMERICA.AL32UTF8
<code>SQL></code><code>select</code> <code>parameter,value</code><code>from</code> <code>nls_session_parameters</code><code>where</code> <code>parameter=</code><code>'NLS_LANGUAGE'</code><code>; </code>
<code>NLS_LANGUAGE AMERICAN</code>
当环境变量NLS_LANG='SIMPLIFIED CHINESE_CHINA.AL32UTF8'