ORA-20011 ORA-01870 Errors when Gathering Fixed Object Stats in 11.2.0.1

A bug found in 11.2.0.1…this occurs when AQ_TM_PROCESSES=0
At the time of this writing MOS didn’t have the information publicly available, I had to open a SR to get the resolution.
 
The fix is to set the database initialization parameter to a non-zero value and gather fixed object statistics again.
 
SQL> exec 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

ORA-06512: at line 1

 

Advertisements

About April C Sims

Oracle DBA for over a decade...enough said.
This entry was posted in Uncategorized and tagged . Bookmark the permalink.

3 Responses to ORA-20011 ORA-01870 Errors when Gathering Fixed Object Stats in 11.2.0.1

  1. ala2000 says:

    alter system set aq_tm_processes=1 scope=memory;

    ORA-20011/ORA-01870 reported when executing dbms_stats.gather_fixed_objects_stats [ID 1083952.1]

    • April C Sims says:

      Yes, that is what I said… see this line:

      The fix is to set the database initialization parameter to a non-zero value and gather fixed object statistics again.

  2. Luis Cova says:

    While upgrading a database from version 10.2.0.5 to 11.2.0.1 these two errors showed up. To recover from them init.ora parameters “shared_pool_size”, “large_pool_size”, “java_pool_size”, “db_cache_size” and “streams_pool_size” were set to ZERO and then issue:
    “alter system set aq_tm_processes=1 scope=memory;” and
    “EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s