dba_tab_modification show num_rows=0 although the stats is gathered for the table
(Doc ID 1364285.1)
Last updated on MARCH 03, 2022
Applies to:
Oracle Database - Enterprise Edition - Version 10.2.0.4 and laterInformation in this document applies to any platform.
Symptoms
- dba_tab_modification show num_rows=0 although the stats is gathered for the table
SQL> select count(*) from ABC;
COUNT(*)
----------
39
SQL> select log_date, operation, job_name from all_scheduler_job_log order by job_name , log_date;
LOG_DATE OPERATION JOB_NAME
--------------------------------------------------------------------------- ------------------------------ -----------------------------------------------------------------
……
06-SEP-11 10.16.13.547874 PM +03:00 RUN GATHER_STATS_JOB
07-SEP-11 10.13.41.045155 PM +03:00 RUN GATHER_STATS_JOB
08-SEP-11 10.12.13.457540 PM +03:00 RUN GATHER_STATS_JOB
09-SEP-11 10.25.09.964891 PM +03:00 RUN GATHER_STATS_JOB
10-SEP-11 06.07.14.961615 AM +03:00 RUN GATHER_STATS_JOB
SQL> Select table_name,to_char(last_analyzed,'yyyy-mm-dd hh24:mi') as last_analyzed, num_rows, stattype_locked , stale_stats
from dba_tab_statistics where owner='ABC' and num_rows=0;
TABLE_NAME LAST_ANALYZED NUM_ROWS STATTYPE_LOCKED STALE_STATS
------------------------ ---------------- ---------- ----- -----------------------------------------------------------------
EXC 2011-09-03 06:07 0 YES
XYZ 2009-12-30 06:51 0 NO
EDP 2011-09-03 06:07 0 YES
ABC 2011-09-03 06:07 0 YES
The stale is yes which means that within the next stats window, statistics should be collected.
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! |
In this Document
Symptoms |
Cause |
Solution |
References |