突然想修改下oracle默认的时间格式,在论坛搜索了一大圈,尝试了很多答案,发现都是谬论,以下是oracle在linux环境下的解决方案。
linux下通过修改spfile文件可以解决,跟环境变量 NLS_DATE_FORMAT毛关系都没有。另外,也测试了windows下只跟环境变量NLS_DATE_FORMAT有关系,跟spfile无关。
今天还搜索到一种方式,通过修改oracle参数文件$ORACLE_HOME/dbs/inita.ora来实现,未验证通过,但按原理来说应该是可以的。这么一个简简单单的问题,耗费了我大量时间啊,一切都是不明白oracle体系结构,执行机制而产生的,要努力掌握这一块!!。
实验环境:linux+oracle 10.2.0.1
windows+oracle 10.2.0.5
[oracle@gzsjmp ~]$ sqlplus sys/haitao88 as sysdba;
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Mar 3 16:02:27 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options
SQL> select sysdate from dual;
SYSDATE ---------- 2013-03-03
SQL> alter system set nls_date_format='YYYY-MM-DD hh24:mi:ss' scope=spfile;
System altered.
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; ORACLE instance started.
Total System Global Area 599785472 bytes Fixed Size 2022632 bytes Variable Size 234881816 bytes Database Buffers 360710144 bytes Redo Buffers 2170880 bytes Database mounted. Database opened. SQL> select sysdate from dual;
SYSDATE ------------------- 2013-03-03 16:04:38
SQL> show parameters nls
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ nls_calendar string nls_comp string nls_currency string nls_date_format string YYYY-MM-DD hh24:mi:ss nls_date_language string
SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options [oracle@gzsjmp ~]$ echo $NLS_DATE_FORMAT; YYYY-MM-DD