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
|
sys>
select
parameter,value
from
nls_instance_parameters
where
parameter=
'NLS_LANGUAGE'
;
PARAMETER VALUE
------------------------------ ------------------------------
NLS_LANGUAGE SIMPLIFIED CHINESE
|
关闭数据库，然后修改环境变量NLS_LANG:
|
1
2
3
4
5
6
7
8
9
10
11
12
|
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
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
[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 它的查询结果默认会从nls_instance_parameters继承，但是如果，我们在环境变量或者通过ALTER SESSION 改变了nls的相关参数，则会覆盖默认值。
例如，当环境变量NLS_LANG=AMERICAN_AMERICA.AL32UTF8
|
1
2
3
4
5
|
SQL>
select
parameter,value
from
nls_session_parameters
where
parameter=
'NLS_LANGUAGE'
;
PARAMETER VALUE
------------------------------ ------------------------------
NLS_LANGUAGE AMERICAN
|
当环境变量NLS_LANG='SIMPLIFIED CHINESE_CHINA.AL32UTF8'
|
1
2
3
4
5
|
SQL>
select
parameter,value
from
nls_session_parameters
where
parameter=
'NLS_LANGUAGE'
;
PARAMETER VALUE
------------------------------ ------------------------------
NLS_LANGUAGE SIMPLIFIED CHINESE
|
