Find the tables/indexes that have stale statistics in a database

Using the code below one can find the tables/indexes that have stale statistics in a database, when options=>’GATHER AUTO’
is used oracle gathers statistics analyzes the tables/indexes that have stale statistics. Table monitoring is
enabled by default in 10g to find table which is used to find table statistics, when STATISTICS_LEVEL is set
to BASIC table monitoring is disabled. In 9i one can enable/disable table monitoring by
calling DBMS_STATS.ALTER_SCHEMA_TABLE_MONITORING

SQL> SET SERVEROUTPUT ON

SQL> DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
dbms_stats.gather_database_stats(objlist=>ObjList, options=>’LIST STALE’);
FOR i in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(i).ownname || ‘.’ || ObjList(i).ObjName || ‘ ‘ || ObjList(i).ObjType || ‘ ‘ || ObjList(i).partname);
END LOOP;
END;
/

SYS.COL_USAGE$ TABLE
SYS.DEPENDENCY$ TABLE
SYS.HISTGRM$ TABLE
SYS.HIST_HEAD$ TABLE


Note: To find schema level stats that are stale one can call
DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>’SCOTT’, objlist=>ObjList, options=>’LIST STALE’);