Oracle RAC中ORA-01843錯誤分析

ORA-01843: not a valid month
Cause:
A date specified an valid month.
Valid month are: January-December, for format code MONTH, and Jan-Dec, for format code MON.
Action:
Enter a valid month value in the correct format.

今天同事說在叢集環境中有報ORA-01843錯誤。

實驗：

SQL> create table test (d date);

Table created.

SQL> insert into test values (to_date('12-JAN-2009','DD-MON-YYYY'));
insert into test values (to_date('12-JAN-2009','DD-MON-YYYY'))
                                 *
ERROR at line 1:
ORA-01843: not a valid month

SQL> col property_name format a30;
SQL> col property_value format a30;
SQL> set linesize 200
SQL> select property_name, property_value
  from database_properties
  2    3   where property_name in
  4         ('NLS_LANGUAGE', 'NLS_TERRITORY', 'NLS_CHARACTERSET');

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CHARACTERSET               ZHS16GBK

SQL> col parameter format a30
SQL> col value format a30
SQL> select *
  2    from NLS_database_PARAMETERS
  3   where parameter in ('NLS_LANGUAGE', 'NLS_TERRITORY', 'NLS_CHARACTERSET');

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CHARACTERSET               ZHS16GBK

SQL> select *
  2    from NLS_instance_PARAMETERS
  3   where parameter in ('NLS_LANGUAGE', 'NLS_TERRITORY', 'NLS_CHARACTERSET');

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_LANGUAGE                   SIMPLIFIED CHINESE
NLS_TERRITORY                  CHINA

SQL> select *
  2    from NLS_session_PARAMETERS
  3   where parameter in ('NLS_LANGUAGE', 'NLS_TERRITORY', 'NLS_CHARACTERSET');

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_LANGUAGE                   SIMPLIFIED CHINESE
NLS_TERRITORY                  CHINA

SQL> show parameters nls_language

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_language                         string      SIMPLIFIED CHINESE

SQL> alter session set nls_language='AMERICAN';

Session altered.

SQL> insert into test values (to_date('13-JAN-2009','DD-MON-YYYY'));

1 row created.

SQL> alter session set nls_language='SIMPLIFIED CHINESE';

Session altered.

SQL> insert into test values (to_date('13-JAN-2009','DD-MON-YYYY'));
insert into test values (to_date('13-JAN-2009','DD-MON-YYYY'))
                                 *
ERROR at line 1:
ORA-01843: not a valid month

SQL> alter session set nls_date_language='AMERICAN';

Session altered.

SQL>  insert into test values (to_date('13-JAN-2009','DD-MON-YYYY'));

1 row created.

SQL> exit   
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[oracle@rhel1 ~]$ export NLS_LANG=AMERICAN
[oracle@rhel1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Mar 1 06:08:12 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL>  insert into test values (to_date('13-JAN-2009','DD-MON-YYYY'));

1 row created.

從以上實驗可以看出，罪魁禍首就是引數檔案中的nls_language引數值。在單機下，nls_language與資料庫的nls_language值是一致的。由於客戶端沒有設定NLS_LANG環境變數，也未設定session級別的nls_language,nls_date_language值。所以插入日期型別就按照例項級別的nls_language來設定，因為引數值是SIMPLIFIED CHINESE,插入的日期格式是to_date('12-JAN-2009','DD-MON-YYYY')而非SIMPLIFIED CHINESE語言的to_date('12-六月-2009','DD-MON-YYYY'),故出現ORA-01843錯誤。

解決方法：

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
或者
alter session set nls_language='AMERICAN';
或者
alter session set nls_date_language='AMERICAN';
或者
alter system set nls_language='AMERICAN' scope=spfile sid='*';

今天發現出現這種錯誤還有另外一種情況：
表結構：
SQL> desc d
 名稱                                      是否為空? 型別
 ----------------------------------------- -------- ---------------
 D                                                  TIMESTAMP(6)

把同事寫的SQL語句列印出來結果是：
insert into d values ('2011-12-12 12:12:12');

執行就會報錯：
SQL> insert into d values ('2011-12-12 12:12:12')
  2  ;
insert into d values ('2011-12-12 12:12:12')
                      *
第 1 行出現錯誤:
ORA-01843: not a valid month

加入to_timestamp函式即可：
SQL> insert into d values (to_timestamp('2011-12-12 12:12:12','yyyy-mm-dd hh24:mi:ss'));

已建立 1 行。