GATHER_STATS_JOB encountered errors. ORA-20000

郑全

[size=130%]DBMS_STATS: GATHER_STATS_JOB encountered errors. ORA-20000: Unable to set values for table DBMS_TABCOMP_TEMP_UNCMP: does not exist or insufficient privileges
[size=130%]

[size=130%]In this Document

Symptoms

Changes

Cause

Solution

References

---

Applies to:   Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Information in this document applies to any platform.

Symptoms

• Alert log shows the error messages below: ...
  Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
  2012-05-24 22:00:13.107000 +02:00
  DBMS_STATS: GATHER_STATS_JOB encountered errors.  Check the trace file.
  Errors in file /data01/oracle/diag/rdbms/octoprd/OCTOPRD2/trace/OCTOPRD2_j002_2772.trc:
  ORA-20000: Unable to set values for table DBMS_TABCOMP_TEMP_UNCMP: does not exist or insufficient privileges
  ...
  
• Complete stack of errors in the trace may also contain: ...
  ORA-20011: Approximate NDV failed:
  ORA-08103: object no longer exists
  ...
  
  

ChangesNone
CauseThis issue matches Bug 9939773 which was closed as not a bug as explained by the following description:
"
The compression advisor creates intermediate tables (creates/drops tables DBMS_TABCOMP_TEMP_UNCMP). In case a GATHER_STATS_JOB is run at the same time , the table(s) will be in the list of tables to be analyzed. In case the table is automatically dropped by the compression advisory job before the GATHER_STATS_JOB has reached to that table in the list, the ORA-2000 will be hit. When DBMS_STATS finds it is no longer available, it writes to the alert log.This is no different from the case where a user table is dropped during a statistics gather.
"
Solution You can ignore this error due to following:

• The error is non-critical.
• Other objects won't be affected by this error and gather stats job continues safely on other user objects. The error will not result in the termination of the gather stats job for the rest of the tables.
• The message in the alert log is only informative.
  

ReferencesNOTE:1247864.1 - DataPump Export Raises ORA-31693 ORA-2354 ORA-942 On DBMS_TABCOMP_TEMP_CMP And DBMS_TABCOMP_TEMP_UNCMP





[size=130%]