find stale stat objects

DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
dbms_stats.gather_schema_stats(ownname=>'VIDSDDL',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;
/

DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
dbms_stats.gather_schema_stats(USER,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;
/


--Below will gather stats(AUTO) and lists objects for which stat gathered(stale)

set serveroutput on
declare
mystaleobjs1 dbms_stats.objecttab;
begin
-- check whether there is any stale objects
dbms_stats.gather_schema_stats(USER, options=>'GATHER AUTO',method_opt=>'FOR ALL COLUMNS SIZE AUTO',cascade=>TRUE,objlist=>mystaleobjs1);
dbms_output.put_line('COPROD Stale Stat refresh Completed for Below Objects. Total Objects: '||mystaleobjs1.count);
dbms_output.put_line('===========================================================================');
dbms_output.put_line('');
for i in 1 .. mystaleobjs1.count loop
dbms_output.put_line(mystaleobjs1(i).ownname||'==>'||mystaleobjs1(i).objtype||'==>'||mystaleobjs1(i).objname||'==>'||mystaleobjs1(i).partname);
end loop;
end;
/