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 行。