nls_date_format参数设置的问题

今天在练习TO_DATE()和TO_CHAR()函数的时候发现了一个问题,就是说nls_date_format参数在会话级alter session修改起作用,可在系统级alter system却不起作用.具体情况如下:

SQL> select sysdate from dual; SYSDATE --------------- 19-OCT-09 SQL> alter session set nls_date_format="MON-DD-YYYY"; Session altered. SQL> select sysdate from dual; SYSDATE ----------------- OCT-19-2009 SQL> ======================================================== SQL> alter system set nls_date_format="MON-DD-YYYY" scope=spfile; System altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 176160768 bytes Fixed Size 1247948 bytes Variable Size 92276020 bytes Database Buffers 79691776 bytes Redo Buffers 2945024 bytes Database mounted. Database opened. SQL> show parameter date NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ fixed_date string nls_date_format string MON-DD-YYYY nls_date_language string SQL> select sysdate from dual; SYSDATE --------------- 19-OCT-09 SQL> 这个时候没有显示OCT-19-2009 而却是19-OCT-09 

上网去搜了搜,找到了答案,那就是优先级的关系导致了这个问题.

系统级alter system的优先级低于会话级alter session的优先级,更加详细的情况接下来会演示出来.

nls参数、环境变量以及函数起作用的顺序如下(由低到高) instance级参数--->nls_lang--->session级参数--->函数 C:>set nls_lang=SIMPLIFIED CHINESE_CHINA.ZHS16GBK C:>sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 7月 29 21:01:38 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. 连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> show parameter nls_date_format NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ nls_date_format string yyyy/mm/dd hh24:mi:ss --很明显实例级别参数nls_date_format没有启作用 SQL> select sysdate from dual; SYSDATE -------------- 29-7月 -09 --============================== --在注册表中清除nls_lang的值，这是我们发现实例级参数nls_date_format：格式 yyyy/mm/dd hh24:mi:ss开始发挥作用 SQL> exit 从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options 断开 C:>sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jul 29 20:54:59 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. ???: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> select sysdate from dual; SYSDATE ------------------- 2009/07/29 20:55:01 --==================================== --恢复注册表中的nls_lang设置：SIMPLIFIED CHINESE_CHINA.ZHS16GBK SQL> exit ? Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options ?? C:>sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 7月 29 20:55:21 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. 连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> select sysdate from dual; SYSDATE -------------- 29-7月 -09 SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; 会话已更改。 --显然session级别的nls_date_format发挥了作用 SQL> select sysdate from dual; SYSDATE ------------------- 2009-07-29 20:55:55 --很显然函数发挥了作用 SQL> select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual; TO_CHAR(SYSDATE,'YY ------------------- 2009-07-29 09:10:01 SQL>

这下就清楚了,呵呵.