ORA-01843 When Generating 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:

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