STALE Column Of The DBA_IND_STATISTICS Is Not Updated When Gathered Statistics For Partitioned Tables (Doc ID 411960.1)

Last updated on SEPTEMBER 18, 2014

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2 to
This problem can occur on any platform.


When statistics are gathered for partitioned tables, the stale column of the user/all/dba_ind_statistics is not updated.

exec dbms_stats.gather_table_stats (ownname=>'<owner_name', tabname=>'table_name', partname=>'partition_name', granularity=>'partition',degree=>8);

select table_owner owner, table_name , partition_name as partition ,stattype_locked as locked, to_char(last_analyzed,'DD-MON-YYYY HH:MI:SS'), stale_stats as stale  from sys.all_ind_statistics ais  WHERE OWNER = 'owner_name' AND PARTITION_NAME IS NOT NULL AND LAST_ANALYZED >= 'dd-MON-yyyy'  AND TO_CHAR(LAST_ANALYZED,'HH24') BETWEEN 'hh' AND 'hh'  order by ais.Last_Analyzed DESC, ais.owner, 
ais.table_owner, ais.table_name, ais.partition_name, ais.index_name;


------------ ---------------------- ------------------------------ -----
-------------------- --- 
<owner_name> <table_name> <partition_name> 
dd-MON-yyyy 08:07:00 YES 



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 hundreds of Community platforms