nls_date_format参数设置的问题

最新推荐文章于 2024-03-28 15:49:43 发布

原创最新推荐文章于 2024-03-28 15:49:43 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #修改时间格式 #database

oracle 专栏收录该内容

26 篇文章

订阅专栏

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

[php] view plain copy

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的优先级,更加详细的情况接下来会演示出来.

[php] view plain copy

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
连接到:
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
???:
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
连接到:
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>

这下就清楚了,呵呵.