ORA-01843 When Generating ASH Report (文档 ID 1147253.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.1.0.3 and later

Information in this document applies to any platform.

***Checked for relevance on 17-Jul-2012***

SYMPTOMS

On : 10.1.0.3 version, RDBMS

When attempting to generate ASH report:

SQL> @?/rdbms/admin/ashrpt.sql

the following error occurs:

ASH Samples in this Workload Repository schema
(to_char((sysdate - to_date(:oldest_smaple, 'DD-Mon-YY HH24:MI:SS'))*1440,
ERROR at line 3:
ORA-01843: not a valid month

CAUSE

The issue is caused by setting nls_language to a language which does not use "MON" mask as date format.

In this example; nls_date_format and nls_date_language are not set explicitly, and nls_language is set to 'SIMPLIFIED CHINESE', which does not use "MON" mask.

Test case:

SQL> alter session set NLS_LANGUAGE='SIMPLIFIED CHINESE';
Session altered.

SQL> select min(sample_time), max(sample_time)
2 from dba_hist_active_sess_history;

MIN(SAMPLE_TIME) MAX(SAMPLE_TIME)
---------------------------------------------------------------------------
29-6月 -10 01.31.31.595 下午 04-7月 -10 08.00.45.965 上午

SQL> alter session set NLS_LANGUAGE='AMERICAN';
Session altered.

SQL> select min(sample_time), max(sample_time)
2 from dba_hist_active_sess_history
3 ;

MIN(SAMPLE_TIME) MAX(SAMPLE_TIME)
---------------------------------------------------------------------------
29-JUN-10 01.31.31.595 PM 04-JUL-10 08.00.45.965 AM

SOLUTION

To implement the solution, please execute the following steps:

Please set nls_language to AMERICAN and re-generate the ASH report:

SQL> alter session set nls_language='AMERICAN';
SQL> @?/rdbms/admin/ashrpt.sql