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

Session altered.
20:15:32 > 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 > 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 > show parameter nls_timestamp

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
nls_timestamp_format                 string
nls_timestamp_tz_format              string
20:24:33 > 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.

Oracle NLS_TIMESTAMP_FORMAT引數

相關文章