Applications Systems

The DPA alert Oracle Stale Statistics is broken

If the Oracle Stale Statistics alert retrieves an object owner name or a partition name that is greater than 30 characters, DPA won't be able to insert that value in the table that it creates. This breaks the alert. You can fix the alert manually by connecting to the monitored Oracle instance and extending the width of the column. ORA error number: ORA-12899.

First published date

11/13/2018 6:56 PM

Last published date

11/13/2018 6:56 PM

Overview

The Oracle Stale Statistics alert returns a list of all tables and indexes that have stale or empty statistics. You may receive Oracle error ORA-12899. Part of the retrieved information is also the owner of the object and the name of the partition, if the table is partitioned. DPA creates a table that allows it to store owner and partition names up to 30 characters long. However, with Oracle 12c and later, the maximum length of those values can be up to 128 characters.

 

If the alert retrieves an object owner name or a partition name that is greater than 30 characters, DPA won't be able to insert that value in the table. This breaks the alert. You can fix the alert manually by connecting to the monitored Oracle instance and extending the width of the column.
 

Environment 

  • DPA, all versions
  • Monitored instance of Oracle 12c or later

Product section

Database Performance Analyzer

Cause

Oracle increased the maximum length of owner and partition name values to 128 characters. The table that DPA creates to store these values cannot store values longer than 30 characters.

Resolution

  1. Stop DPA .
  2. Connect to the monitored Oracle instance and run the following SQL statements:
    ALTER TABLE ignite_temp_stats MODIFY obj_partition VARCHAR2(128);
    ALTER TABLE ignite_temp_stats MODIFY obj_owner VARCHAR2(128);
  3. Start DPA.