【转】NLS 视图的研究

Oracle 中有这么几个关于 NLS 的视图：

NLS_DATABASE_PARAMETERS
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 的《字符集问题的初步探讨(五)》。

转载于:https://www.cnblogs.com/wzc998/archive/2010/11/17/1879652.html