Oracle rac ora29283,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 sessionset 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 sessionset 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 行。