Find out tables with Outdated statistics

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;
/

To collect statistics of a outdated table of SCOTT schema:


exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMPLOYEE', cascade=>true, degree=>2, estimate_percent=>50, method_opt=>'FOR ALL COLUMNS SIZE 1');

To find schema level stats that are stale one can call-


DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'SCOTT', objlist=>ObjList, options=>'LIST STALE');