ORA-20011/ORA-01870 reported when executing dbms_stats.gather_fixed_objects_stats
Last updated on FEBRUARY 08, 2017
Applies to:
Oracle Database - Enterprise Edition - Version 11.1.0.6 to 11.2.0.2 [Release 11.1 to 11.2]Information in this document applies to any platform.
Symptoms
ORA-20011 ORA-01870 reported when executing dbms_stats.gather_fixed_objects_stats
SQL> execute dbms_stats.gather_fixed_objects_stats;
BEGIN dbms_stats.gather_fixed_objects_stats; END;
ERROR at line 1:
ORA-20011: Approximate NDV failed: ORA-01870: the intervals or datetimes are not mutually comparable
ORA-06512: at "SYS.DBMS_STATS", line 20508
ORA-06512: at "SYS.DBMS_STATS", line 20951
ORA-06512: at "SYS.DBMS_STATS", line 21498
BEGIN dbms_stats.gather_fixed_objects_stats; END;
ERROR at line 1:
ORA-20011: Approximate NDV failed: ORA-01870: the intervals or datetimes are not mutually comparable
ORA-06512: at "SYS.DBMS_STATS", line 20508
ORA-06512: at "SYS.DBMS_STATS", line 20951
ORA-06512: at "SYS.DBMS_STATS", line 21498
Setting an errorstack on the ORA-01870 indicates that the failing internal SQL is a select from X$KWQITCX, for example:
Current SQL Statement for this session (sql_id=0dcdf9wz8znm7) -----
/* SQL Analyze(0) */ select /*+ full(t) no_parallel(t)
no_parallel_index(t) dbms_stats cursor_sharing_exact
use_weak_name_resl dynamic_sampling(0) no_monitoring
...
from "SYS"."X$KWQITCX" t
...
/* SQL Analyze(0) */ select /*+ full(t) no_parallel(t)
no_parallel_index(t) dbms_stats cursor_sharing_exact
use_weak_name_resl dynamic_sampling(0) no_monitoring
...
from "SYS"."X$KWQITCX" t
...
Changes
The Oracle RDBMS was recently upgraded from Oracle10g to Oracle11g.
Cause
Sign In with your My Oracle Support account |
|
Don't have a My Oracle Support account? Click to get started |
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms