SQL> Select *
2 from table(dbms_workload_repository.ASH_report_html(
3 l_dbid => 782550019,
4 l_inst_num => 1,
5 l_btime => TO_DATE('07/02/2013 20:00:00', 'MM/DD/YYYY HH24:MI:SS'),
6 l_etime => TO_DATE('07/02/2013 20:59:59', 'MM/DD/YYYY HH24:MI:SS'),
7 l_options => 0,
8 l_slot_width => 0,
9 l_sid => null,
10 l_sql_id => null,
11 l_wait_class => null,
12 l_service_hash => null,
13 l_module => null,
14 l_action => null,
15 l_client_id => null,
16 l_plsql_entry => null))
17 ;
from table(dbms_workload_repository.ASH_report_html(
*
ERROR at line 2:
ORA-01843: not a valid month
ORA-06512: at "SYS.DBMS_SWRF_REPORT_INTERNAL", line 11246
ORA-06512: at "SYS.DBMS_SWRF_REPORT_INTERNAL", line 10668
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 1554
ORA-06512: at line 1
SQL>
So error is here again, what relief Toad from any responsibility.
Running same db with some other date (very recent one)...I got proper and ok result:
SQL> Select *
2 from table(dbms_workload_repository.ASH_report_html(
3 l_dbid => 782550019,
4 l_inst_num => 1,
5 l_btime => TO_DATE('07/09/2013 10:00:00', 'MM/DD/YYYY HH24:MI:SS'),
6 l_etime => TO_DATE('07/09/2013 10:59:59', 'MM/DD/YYYY HH24:MI:SS'),
7 l_options => 0,
8 l_slot_width => 0,
9 l_sid => null,
10 l_sql_id => null,
11 l_wait_class => null,
12 l_service_hash => null,
13 l_module => null,
14 l_action => null,
15 l_client_id => null,
16 l_plsql_entry => null))
17 ;
OUTPUT
--------------------------------------------------------------------------------
<html lang="en"><head><title>ASH Report - From 09-Jul-13 10:00:00 To 09-Jul-13 10:59:59</title>
<style type="text/css">
...
...
<style type="text/css">
</table><p />
<br /><a class="awr" href="#top">Back to Top</a><p />
<p />
End of Report
</body></html>
464 rows selected.
SQL>
The same was running through Toad interface for the same period-as expected ...
Obviously date period was a problem what lead me to successfully ending.
The Solution
First let me show date when all this was happening.SQL> select to_char (sysdate ,'dd.mm.yyyy hh24:mi:ss') D from dual; D ------------------- 09.07.2013 13:48:31Then I tried to see what ASH data are in SGA, V$ACTIVE_SESSION_HISTORY:
SQL> select min (sample_time), max(sample_time) from V$ACTIVE_SESSION_HISTORY; MIN(SAMPLE_TIME) MAX(SAMPLE_TIME) ------------------------------- ---------------------------- 08.07.13 23:09:36,411 09.07.13 13:40:56,653So I tried to call period out of V$ACTIVE_SESSION_HISTORY sample_time, which fall in DBA_HIST_ACTIVE_SESS_HISTORY view. Again, action result an error!
Trying to execute the problematic period through recommended Oracle scripts (ashrpt.sql/ashrpti.sql from rdbms/admin directory), all went fine, with no error.
Because all date based errors usually fall in some kind of NLS problem, I check what is in mine session:
SQL> col parameter for a30; SQL> col VALUE for a30; SQL> SQL> select * from NLS_SESSION_PARAMETERS; PARAMETER VALUE ------------------------------ ------------------------------ NLS_LANGUAGE CROATIAN NLS_TERRITORY CROATIA NLS_CURRENCY kn NLS_ISO_CURRENCY CROATIA NLS_NUMERIC_CHARACTERS ,. NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD.MM.RR NLS_DATE_LANGUAGE CROATIAN NLS_SORT CROATIAN NLS_TIME_FORMAT HH24:MI:SSXFF NLS_TIMESTAMP_FORMAT DD.MM.RR HH24:MI:SSXFF NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR NLS_TIMESTAMP_TZ_FORMAT DD.MM.RR HH24:MI:SSXFF TZR NLS_DUAL_CURRENCY kn NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE 17 rows selected. SQL>From the time when RMAN was not working properly if NLS_LANG was not set to AMERICAN_AMERICA, I remember many problems. So, I changed session parameters to it:
SQL> alter session set NLS_LANGUAGE='AMERICAN'; Session altered. SQL> alter session set NLS_TERRITORY='AMERICA'; Session altered. SQL> select * from NLS_SESSION_PARAMETERS; PARAMETER VALUE ------------------------------ ------------------------------ NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY $ NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE 17 rows selected. SQL>When I run again problematic period, all went without error:
SQL> Select *
2 from table(dbms_workload_repository.ASH_report_html(
3 l_dbid => 782550019,
4 l_inst_num => 1,
5 l_btime => TO_DATE('07/02/2013 20:00:00', 'MM/DD/YYYY HH24:MI:SS'),
6 l_etime => TO_DATE('07/02/2013 20:59:59', 'MM/DD/YYYY HH24:MI:SS'),
7 l_options => 0,
8 l_slot_width => 0,
9 l_sid => null,
10 l_sql_id => null,
11 l_wait_class => null,
12 l_service_hash => null,
13 l_module => null,
14 l_action => null,
15 l_client_id => null,
16 l_plsql_entry => null))
17 ;
OUTPUT
--------------------------------------------------------------------------------
<html lang="en"><head><title>ASH Report - From 02-Jul-13 20:00:00 To 02-Jul-13 20:59:59</title>
<style type="text/css">
...
...
<style type="text/css">
</table><p />
<br /><a class="awr" href="#top">Back to Top</a><p />
<p />
End of Report
</body></html>
464 rows selected.
SQL>
Works!
The End
As you see, if you run ASH_report_html call in period which fall in V$ACTIVE_SESSION_HISTORY, then your NLS settings has no influence at all.If you run period outside of it, Oracle programmers has hard codded some where date picture in ASH_report_html ... to apply AMERICAN settings.
Be aware that some bad codding doesn't end soon...even if they come from Oracle.
Hope this helps someone.
Cheers!
