ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle NLS_TIMESTAMP_FORMAT参数

Oracle NLS_TIMESTAMP_FORMAT参数

原创 Linux操作系统 作者:regonly1 时间:2009-08-05 20:18:39 0 删除 编辑
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
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

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

Session altered.

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-05-10

  • 博文量
    257
  • 访问量
    1022216