Oracle NLS_TIMESTAMP_FORMAT参数-CSDN博客

20:14:42 lyon@ORCL> alter session set nls_timestamp_format = 'yyyy-mm-dd hh24.mi.ss.ff';

Session altered.
20:15:32 lyon@ORCL> select ora_rowscn, scn_to_timestamp(ora_rowscn) from ow_subscriber where rownum <= 10;

ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------- ---------------------------------------------------------------------------
    935854 2009-05-26 04.09.15.000000000
    935854 2009-05-26 04.09.15.000000000
    935854 2009-05-26 04.09.15.000000000
    935854 2009-05-26 04.09.15.000000000
    935854 2009-05-26 04.09.15.000000000
    935854 2009-05-26 04.09.15.000000000
    935854 2009-05-26 04.09.15.000000000
    935854 2009-05-26 04.09.15.000000000
    935854 2009-05-26 04.09.15.000000000
    935854 2009-05-26 04.09.15.000000000

10 rows selected.

20:15:57 lyon@ORCL> alter system set nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff' scopt=spfile;
alter system set nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff' scopt=spfile
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option

在修改system层面的这个参数时，出现了该参数在该选项下是不能被修改的错误（即system级别下不能修改）。

该如何在这个级别修改呢？

通过在操作系统层面修改这个参数，发现实现了这个目的：

通过在.bash_profile中的oracle环境参数配置区加入如下环境变量：

export NLS_TIMESTAMP_FORMAT='yyyy-mm-dd hh24:mi:ss.ff'

[oracle@localhost ~]$ !so
source .bash_profile
[oracle@localhost ~]$ echo $NLS_TIMESTAMP_FORMAT
yyyy-mm-dd hh24:mi:ss.ff

重启数据库，检查数据：

[oracle@localhost ~]$ sqlplus lyon/passwd

SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 8月 5 20:24:27 2009

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Session altered.

20:24:27 lyon@ORCL> show parameter nls_timestamp

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
nls_timestamp_format                 string
nls_timestamp_tz_format              string
20:24:33 lyon@ORCL> select ora_rowscn, scn_to_timestamp(ora_rowscn) from ow_subscriber where rownum <= 10;

ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------- ---------------------------------------------------------------------------
    935854 2009-05-26 04:09:15.000000000
    935854 2009-05-26 04:09:15.000000000
    935854 2009-05-26 04:09:15.000000000
    935854 2009-05-26 04:09:15.000000000
    935854 2009-05-26 04:09:15.000000000
    935854 2009-05-26 04:09:15.000000000
    935854 2009-05-26 04:09:15.000000000
    935854 2009-05-26 04:09:15.000000000
    935854 2009-05-26 04:09:15.000000000
    935854 2009-05-26 04:09:15.000000000

10 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12932950/viewspace-611445/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12932950/viewspace-611445/