nls_timestamp_format参数在11.2中的变化

nls_timestamp_format参数在11.2.0.2及以后版本通过pfile或spfile或都不能进行修改了,在会话级还是能进行修改,Oracle提供若干NLS参数定制数据库和客户机以适应本地格式,例如有NLS_LANGUAGE,NLS_DATE_FORMAT,NLS_CALENDER等,可以通过查询以下数据字典或v$视图查看。
NLS_DATABASE_PARAMETERS:显示数据库当前NLS参数取值,包括数据库字符集取值
NLS_SESSION_PARAMETERS:显示由NLS_LANG设置的参数,或经过alter session改变后的参数值(不包括由NLS_LANG设置的客户端字符集)
NLS_INSTANCE_PARAMETE:显示由参数文件init.ora定义的参数
V$NLS_PARAMETERS:显示数据库当前NLS参数取值

使用下列方法可以修改NLS参数
(1)更新props$
(2)修改实例启动时使用的初始化参数文件
(3)修改环境变量NLS_LANG
(4)使用ALTER SESSION语句,在oracle会话中修改
(5)使用某些SQL函数

NLS作用优先级别:Sql function > alter session >环境变量或注册表>参数文件>数据库默认参数。如果会话级与实例级别和数据库级别参数不一致,就会以会话级的为准,因为会话级别的参数优先级高于实例级别和数据库级别的参数。客户端的环境变量或注册表会对会话参数产生影响,比如NLS_LANG参数
NLS_LANG==language_territory.client character set
Language:显示oracle消息,校验,日期命名
Territory:指定默认日期、数字、货币等格式
Client character set:指定客户端将使用的字符集
例如:NLS_LANG=AMERICAN_AMERICA.US7ASCII
AMERICAN是语言,AMERICA是地区,US7ASCII是客户端字符集,那么就会影响 nls_date_format,nls_timestamp_foramt等日期的格式。

下面通过操作来进行验证。
在会话级进行修改

SQL> select version from v$instance;

VERSION
-----------------
11.2.0.4.0

SQL> select a.name,a.VALUE from v$parameter a where a.name='nls_timestamp_format';

NAME                           VALUE
------------------------------ ----------------------------------------
nls_timestamp_format           DD-MON-RR HH.MI.SSXFF AM

将nls_timestamp_format参数设置成yyyy-mm-dd hh24:mi:ssxff格式

SQL> alter session set NLS_TIMESTAMP_FORMAT='yyyy-mm-dd hh24:mi:ssxff';

Session altered.

查看是否在会话级修改成功

SQL>  select * from NLS_SESSION_PARAMETERS;

PARAMETER                                                    VALUE
------------------------------------------------------------ ----------------------------------------
NLS_LANGUAGE                                                 AMERICAN
NLS_TERRITORY                                                AMERICA
NLS_CURRENCY                                                 $
NLS_ISO_CURRENCY                                             AMERICA
NLS_NUMERIC_CHARACTERS                                       .,
NLS_CALENDAR                                                 GREGORIAN
NLS_DATE_FORMAT                                              DD-MON-RR
NLS_DATE_LANGUAGE                                            AMERICAN
NLS_SORT                                                     BINARY
NLS_TIME_FORMAT                                              HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT                                         yyyy-mm-dd hh24:mi:ssxff

PARAMETER                                                    VALUE
------------------------------------------------------------ ----------------------------------------
NLS_TIME_TZ_FORMAT                                           HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT                                      DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY                                            $
NLS_COMP                                                     BINARY
NLS_LENGTH_SEMANTICS                                         BYTE
NLS_NCHAR_CONV_EXCP                                          FALSE

17 rows selected.

SQL> show parameter nls_timestamp_format

NAME                                 TYPE                           VALUE
------------------------------------ ------------------------------ ------------------------------
nls_timestamp_format                 string                         yyyy-mm-dd hh24:mi:ssxff

从上面的查询可以看到在会话级nls_timestamp_format参数设置成了yyyy-mm-dd hh24:mi:ssxff格式。

下面通过alter system语句与spfile参数文件来在系统级别进行修改

SQL> alter system set NLS_TIMESTAMP_FORMAT='yyyy-mm-dd hh24:mi:ssxff' scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  669581312 bytes
Fixed Size                  1366724 bytes
Variable Size             306185532 bytes
Database Buffers          356515840 bytes
Redo Buffers                5513216 bytes
Database mounted.
Database opened.

重新启动实例后,查看nls_timestamp_format参数的值没有被修改

SQL> show parameter nls_timestamp_format

NAME                                 TYPE                           VALUE
------------------------------------ ------------------------------ ------------------------------
nls_timestamp_format                 string                         DD-MON-RR HH.MI.SSXFF AM

通过pfile来检查spfile文件中nls_timestamp_format参数的设置可以发现,alter system对nls_timestamp_format的修改并没有存储到spfile文件中。

SQL> create pfile='$ORACLE_BASE/inittemp1.ora' from spfile; 

File created.
[oracle@rac2 oracle]$ cat inittemp.ora
rac2.__db_cache_size=419430400
rac1.__db_cache_size=419430400
rac1.__java_pool_size=4194304
rac2.__java_pool_size=4194304
rac2.__large_pool_size=8388608
rac1.__large_pool_size=8388608
rac2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
rac1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
rac1.__pga_aggregate_target=125829120
rac2.__pga_aggregate_target=125829120
rac1.__sga_target=671088640
rac2.__sga_target=671088640
rac1.__shared_io_pool_size=0
rac2.__shared_io_pool_size=0
rac1.__shared_pool_size=230686720
rac2.__shared_pool_size=230686720
rac1.__streams_pool_size=0
rac2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/rac/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+DATADG/rac/controlfile/current.265.864929297'
*.db_block_size=8192
*.db_create_file_dest='+DATADG'
*.db_domain=''
*.db_name='rac'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racXDB)'
rac2.instance_number=2
rac1.instance_number=1
*.open_cursors=300
*.pga_aggregate_target=125829120
*.processes=150
*.remote_listener='rac-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_max_size=671088640
*.sga_target=671088640
rac2.thread=2
rac1.thread=1
#rac1.undo_tablespace='UNDOTBS1'
#rac2.undo_tablespace='UNDOTBS2'

手工向pfile参数文件中增加nls_timestamp_format参数设置

[oracle@rac2 oracle]$ cat inittemp.ora
rac2.__db_cache_size=419430400
rac1.__db_cache_size=419430400
rac1.__java_pool_size=4194304
rac2.__java_pool_size=4194304
rac2.__large_pool_size=8388608
rac1.__large_pool_size=8388608
rac2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
rac1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
rac1.__pga_aggregate_target=125829120
rac2.__pga_aggregate_target=125829120
rac1.__sga_target=671088640
rac2.__sga_target=671088640
rac1.__shared_io_pool_size=0
rac2.__shared_io_pool_size=0
rac1.__shared_pool_size=230686720
rac2.__shared_pool_size=230686720
rac1.__streams_pool_size=0
rac2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/rac/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+DATADG/rac/controlfile/current.265.864929297'
*.db_block_size=8192
*.db_create_file_dest='+DATADG'
*.db_domain=''
*.db_name='rac'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racXDB)'
rac2.instance_number=2
rac1.instance_number=1
*.open_cursors=300
*.pga_aggregate_target=125829120
*.processes=150
*.remote_listener='rac-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_max_size=671088640
*.sga_target=671088640
rac2.thread=2
rac1.thread=1
nls_timestamp_format='yyyy-mm-dd hh24:mi:ssxff'

使用增加nls_timestamp_format参数的pfile文件来启动数据库,报错不能启动数据库。

SQL> startup pfile='$ORACLE_BASE/inittemp.ora'
ORACLE instance started.

Total System Global Area  669581312 bytes
Fixed Size                  1366724 bytes
Variable Size             306185532 bytes
Database Buffers          356515840 bytes
Redo Buffers                5513216 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-30012: undo tablespace 'hh24:mi:ssxff'' does not exist or of wrong type
Process ID: 7669
Session ID: 1 Serial number: 5

尝试通过props$来进行修改

SQL> update sys.props$ set value$='yyyy-mm-dd hh24:mi:ssxff' where name='NLS_TIMESTAMP_FORMAT'; 

1 row updated.

SQL> commit;

Commit complete.

SQL> select name, value$ from sys.props$ where name='NLS_TIMESTAMP_FORMAT';

NAME
------------------------------
VALUE$
--------------------------------------------------------------------------------
NLS_TIMESTAMP_FORMAT
yyyy-mm-dd hh24:mi:ssxff

修改之后查看数据库级别nls_timestamp_format参数值已经修改成功

SQL> select parameter,value from nls_database_parameters where parameter='NLS_TIMESTAMP_FORMAT';

PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_TIMESTAMP_FORMAT
yyyy-mm-dd hh24:mi:ssxff

修改之后查看实例级别nls_timestamp_format参数值已经修改成功

SQL> select parameter,value from nls_instance_parameters where parameter='NLS_TIMESTAMP_FORMAT';

PARAMETER
------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_TIMESTAMP_FORMAT
yyyy-mm-dd hh24:mi:ssxff

修改之后查看会话级别nls_timestamp_format参数值没有修改成功

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

PARAMETER
------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_TIMESTAMP_FORMAT
DD-MON-RR HH.MI.SSXFF AM

查看客户端环境变量nls_lang= AMERICAN_AMERICA.ZHS16GBK,所以在会话级nls_timestamp_format的格式仍然为DD-MON-RR HH.MI.SSXFF AM

[oracle@rac2 ~]$ vi .bash_profile
# .bash_profile

# Get the aliases and functions
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

TEMP=/u01/tmp
TMPDIR=/u01/tmp
export TEMP TMPDIR
export LD_ASSUME_KERNEL=2.6.9
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db
export ORACLE_SID=rac2
export ORACLE_UNQNAME=rac
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib
export LD_LIBRARY_PATH
export PATH=$PATH:$ORACLE_HOME/bin
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib
export CLASSPATH

修改环境变量,这里选择去掉nls_lang

[oracle@rac2 ~]$ vi .bash_profile
# .bash_profile

# Get the aliases and functions
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

TEMP=/u01/tmp
TMPDIR=/u01/tmp
export TEMP TMPDIR
export LD_ASSUME_KERNEL=2.6.9
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db
export ORACLE_SID=rac2
export ORACLE_UNQNAME=rac
#export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib
export LD_LIBRARY_PATH
export PATH=$PATH:$ORACLE_HOME/bin
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib
export CLASSPATH

再次通过客户端连接到数据库查看nls_timestamp_foramt参数

[oracle@rac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 18 09:01:23 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

数据库级别nls_timestamp_foramt参数为修改后的yyyy-mm-dd hh24:mi:ssxff

SQL> select parameter,value from nls_database_parameters where parameter='NLS_TIMESTAMP_FORMAT';

PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_TIMESTAMP_FORMAT
yyyy-mm-dd hh24:mi:ssxff

实例级别nls_timestamp_foramt参数为修改后的yyyy-mm-dd hh24:mi:ssxff

SQL> select parameter,value from nls_instance_parameters where parameter='NLS_TIMESTAMP_FORMAT';

PARAMETER
------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_TIMESTAMP_FORMAT
yyyy-mm-dd hh24:mi:ssxff

会话级别nls_timestamp_foramt参数为修改后的yyyy-mm-dd hh24:mi:ssxff,说明nls_lang环境变量没有对会话级产生影响了。

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

PARAMETER
------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_TIMESTAMP_FORMAT
yyyy-mm-dd hh24:mi:ssxff


SQL> show parameter nls

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_calendar                         string
nls_comp                             string      BINARY
nls_currency                         string
nls_date_format                      string
nls_date_language                    string
nls_dual_currency                    string
nls_iso_currency                     string
nls_language                         string      AMERICAN
nls_length_semantics                 string      BYTE
nls_nchar_conv_excp                  string      FALSE
nls_numeric_characters               string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_sort                             string
nls_territory                        string      AMERICA
nls_time_format                      string
nls_time_tz_format                   string
nls_timestamp_format                 string      yyyy-mm-dd hh24:mi:ssxff
nls_timestamp_tz_format              string

SQL> select name,value from v$parameter where name='nls_timestamp_format';

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
nls_timestamp_format
yyyy-mm-dd hh24:mi:ssxff

用远程客户机(windows)用plsql连接需要设置环境变量nls_timestamp_foramt,将nls_timestamp_format设置成与props$中nls_timestamp_format参数相同的参数值,以确保会话级与实例级和数据库级一致,避免不一致所产生的问题。在设置完环境变后,用plsql连接数据库来进行检查

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 
Connected as SYS
 
SQL> show parameter nls
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_calendar                         string      GREGORIAN
nls_comp                             string      BINARY
nls_currency                         string      $
nls_date_format                      string      DD-MON-RR
nls_date_language                    string      AMERICAN
nls_dual_currency                    string      $
nls_iso_currency                     string      AMERICA
nls_language                         string      AMERICAN
nls_length_semantics                 string      BYTE
nls_nchar_conv_excp                  string      FALSE
nls_numeric_characters               string      .,
nls_sort                             string      BINARY
nls_territory                        string      AMERICA
nls_time_format                      string      HH.MI.SSXFF AM
nls_time_tz_format                   string      HH.MI.SSXFF AM TZR
nls_timestamp_format                 string      yyyy-mm-dd hh24:mi:ssxff
nls_timestamp_tz_format              string      DD-MON-RR HH.MI.SSXFF AM TZR
 
SQL> select name,value from v$parameter where name='nls_timestamp_format';
 
NAME                                                                             VALUE
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
nls_timestamp_format                                                             yyyy-mm-dd hh24:mi:ssxff
 
SQL> select to_timestamp('2015-08-18 09:39:01.19','yyyy-mm-dd hh24:mi:ssxff') from dual;
 
TO_TIMESTAMP('2015-08-1809:39:
--------------------------------------------------------------------------------
2015-08-18 09:39:01.190000000

总结:
Nls_timestamp_format在数在11.2.0.2及以后版本不能通过pfile或spfile来在实例级别进行修改。要想在实例级修改nls_timestamp_format可以通过props$来进行修改,这是在数据库级别进行修改,但由于不能在实例级别设置,那么实例级会继承数据库级别的设置,因此只要在数据库级别设置了nls_timestamp_format,那么实例级别的nls_timestamp_format也就等于设置了。

客户端的环境变量(UNIX/Linux)nls_lang会在会话级别影响nls_timestamp_format。

客户端的环境变量(windows)nls_timestamp_format会在会话级别影响nls_timestamp_format。

 

发表评论

电子邮件地址不会被公开。