My Oracle Support Banner

ORA-20011/ORA-01870 reported when executing dbms_stats.gather_fixed_objects_stats (Doc ID 1083952.1)

Last updated on DECEMBER 06, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 to 11.2.0.2 [Release 11.1 to 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
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



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
...

Changes

The Oracle RDBMS was recently upgraded from Oracle10g to Oracle11g. 

Cause

To view full details, 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 a vibrant support community of peers and Oracle experts.