【转】NLS 视图的研究
Oracle 中有这么几个关于 NLS 的视图：
NLS_INSTANCE_PARAMETERS
NLS_SESSION_PARAMETERS
V$NLS_PARAMETERS
V$NLS_VALID_VALUES
可以通过 V$FIXED_VIEW_DEFINITION 和 DBA_VIEWS 来看看，他们的定义，来了解他们可以给我们反映一些什么信息：
STSC@MLIV> SELECT TEXT FROM DBA_VIEWS
2 WHERE VIEW_NAME='NLS_DATABASE_PARAMETERS';
TEXT
-------------------------------------------------------------
select name, substr(value$, 1, 40)
from props$
where name like 'NLS%'
STSC@MLIV> ED
Wrote file afiedt.buf
1 SELECT TEXT FROM DBA_VIEWS
2* WHERE VIEW_NAME='NLS_INSTANCE_PARAMETERS'
STSC@MLIV> /
TEXT
-------------------------------------------------------------
select substr(upper(name), 1, 30), substr(value, 1, 40)
from v$parameter
where name like 'nls%'
STSC@MLIV> SELECT TEXT FROM DBA_VIEWS
2 WHERE VIEW_NAME='NLS_SESSION_PARAMETERS';
TEXT
-------------------------------------------------------------
select substr(parameter, 1, 30), substr(value, 1, 40)
from v$nls_parameters
where parameter != 'NLS_CHARACTERSET' and
parameter != 'NLS_NCHAR_CHARACTERSET'
可以看到，NLS_DATABASE_PARAMETERS、NLS_INSTANCE_PARAMETERS、NLS_SESSION_PARAMETERS
分别来自于 props$、v$parameters、v$nls_parameters，props$ 存储的是数据库创建时的各项参数选项，也就是数据库的属性；v$parameters 来自于 init 参数，是实例的属性，v$nls_parameters 可以再通过以下的过程，研究它来自于何处：
STSC@MLIV> select view_definition from V$FIXED_VIEW_DEFINITION
2 where VIEW_NAME='V$NLS_PARAMETERS';
VIEW_DEFINITION
----------------------------------------------------------------------------------------------------
select PARAMETER , VALUE from GV$NLS_PARAMETERS where inst_id = USERENV('Instance')
STSC@MLIV> ed
Wrote file afiedt.buf
1 select view_definition from V$FIXED_VIEW_DEFINITION
2* where VIEW_NAME='GV$NLS_PARAMETERS'
STSC@MLIV> /
VIEW_DEFINITION
----------------------------------------------------------------------------------------------------
select inst_id,parameter, value from x$nls_parameters where parameter != 'NLS_SPECIAL_CHARS'
SYS@MLIV> SELECT * FROM x$nls_parameters;
ADDR INDX INST_ID PARAMETER
-------- ---------- ---------- -------------------------------------------
---------------
VALUE
----------------------------------------------------------------
5E315788 0 1 NLS_LANGUAGE
AMERICAN
5E315788 1 1 NLS_TERRITORY
CHINA
5E315788 2 1 NLS_CURRENCY
RMB
5E315788 3 1 NLS_ISO_CURRENCY
CHINA
5E315788 4 1 NLS_NUMERIC_CHARACTERS
.,
5E315788 5 1 NLS_CALENDAR
GREGORIAN
5E315788 6 1 NLS_DATE_FORMAT
RRRR-MM-DD
5E315788 7 1 NLS_DATE_LANGUAGE
AMERICAN
5E315788 8 1 NLS_CHARACTERSET
ZHS16GBK
5E315788 9 1 NLS_SORT
BINARY
5E315788 10 1 NLS_TIME_FORMAT
HH.MI.SSXFF AM
5E315788 11 1 NLS_TIMESTAMP_FORMAT
DD-MON-RR HH.MI.SSXFF AM
5E315788 12 1 NLS_TIME_TZ_FORMAT
HH.MI.SSXFF AM TZH:TZM
5E315788 13 1 NLS_TIMESTAMP_TZ_FORMAT
DD-MON-RR HH.MI.SSXFF AM TZH:TZM
5E315788 14 1 NLS_DUAL_CURRENCY
RMB
5E315788 15 1 NLS_SPECIAL_CHARS
|@
5E315788 16 1 NLS_NCHAR_CHARACTERSET
ZHS16GBK
5E315788 17 1 NLS_COMP
BINARY
18 rows selected.
SYS@MLIV> alter session set NLS_LANGUAGE='SIMPLIFIED CHINESE';
靠靠靠
SYS@MLIV> SELECT * FROM x$nls_parameters;
ADDR INDX INST_ID PARAMETER
-------- ---------- ---------- -------------------------------------------
---------------
VALUE
----------------------------------------------------------------
5E315788 0 1 NLS_LANGUAGE
SIMPLIFIED CHINESE
5E315788 1 1 NLS_TERRITORY
CHINA
5E315788 2 1 NLS_CURRENCY
RMB
5E315788 3 1 NLS_ISO_CURRENCY
CHINA
5E315788 4 1 NLS_NUMERIC_CHARACTERS
.,
5E315788 5 1 NLS_CALENDAR
GREGORIAN
5E315788 6 1 NLS_DATE_FORMAT
RRRR-MM-DD
5E315788 7 1 NLS_DATE_LANGUAGE
SIMPLIFIED CHINESE
5E315788 8 1 NLS_CHARACTERSET
ZHS16GBK
5E315788 9 1 NLS_SORT
BINARY
5E315788 10 1 NLS_TIME_FORMAT
HH.MI.SSXFF AM
5E315788 11 1 NLS_TIMESTAMP_FORMAT
DD-MON-RR HH.MI.SSXFF AM
5E315788 12 1 NLS_TIME_TZ_FORMAT
HH.MI.SSXFF AM TZH:TZM
5E315788 13 1 NLS_TIMESTAMP_TZ_FORMAT
DD-MON-RR HH.MI.SSXFF AM TZH:TZM
5E315788 14 1 NLS_DUAL_CURRENCY
RMB
5E315788 15 1 NLS_SPECIAL_CHARS
|@
5E315788 16 1 NLS_NCHAR_CHARACTERSET
ZHS16GBK
5E315788 17 1 NLS_COMP
BINARY
靠?8靠
SYS@MLIV> SELECT * FROM NLS_SESSION_PARAMETERS;
PARAMETER
------------------------------------------------------------
VALUE
--------------------------------------------------------------------------
NLS_LANGUAGE
SIMPLIFIED CHINESE
NLS_TERRITORY
CHINA
NLS_CURRENCY
RMB
NLS_ISO_CURRENCY
CHINA
NLS_NUMERIC_CHARACTERS
.,
NLS_CALENDAR
GREGORIAN
NLS_DATE_FORMAT
RRRR-MM-DD
NLS_DATE_LANGUAGE
SIMPLIFIED CHINESE
NLS_SORT
BINARY
NLS_TIME_FORMAT
HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT
DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT
HH.MI.SSXFF AM TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT
DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY
RMB
NLS_COMP
BINARY
靠?5靠
可以看到，用 alter session 对客户端的参数的改变会反映到基表 x$nls_parameters 上。
结论：
NLS_DATABASE_PARAMETERS 显示数据库各属性参数
NLS_INSTANCE_PARAMETERS 显示实例参数（由 init 指定）
NLS_SESSION_PARAMETERS 来自于 V$NLS_PARAMETERS，显示客户端的参数
V$NLS_PARAMETERS 显示客户端的参数
V$NLS_VALID_VALUES 所有可用的 NLS 参数
注：V$NLS_PARAMETERS 中的 NLS_CHARACTERSET 和 NLS_NCHAR_CHARACTERSET 不会因为客户端环境变量 NLS_LANG 的指定而改变，他和 NLS_DATABASE_PARAMETERS 中这两个参数的区别是，前者影响导入操作，后者影响数据存储，详细可参考 eygle 的《字符集问题的初步探讨(五)》。
