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。
