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!
Hi Damir,
ReplyDeleteI hit the same problem with POLISH language settings. Thanks for sharing.
Hvala.
Bartosz