To list the objects that are stale:
declare
mylist dbms_stats.ObjectTab;
begin
-- GET list of missing stats
dbms_output.put_line( 'BEFORE' );
dbms_stats.gather_schema_stats(ownname=>'DBAADMIN',options=>'LIST STALE',cascade=>true,objlist=>mylist);
FOR i IN 1..mylist.count
LOOP
dbms_output.put_line( mylist(i).ownname||'.'||mylist(i).objname||' '||mylist(i).objtype );
END LOOP;
-- GATHER STATS
dbms_stats.gather_schema_stats(ownname=>'DBAADMIN',options=>'GATHER STALE',cascade=>true );
-- CHECK LIST AGAIN
dbms_output.put_line( 'AFTER' );
dbms_stats.gather_schema_stats(ownname=>'DBAADMIN',options=>'LIST STALE',cascade=>true,objlist=>mylist);
FOR i IN 1..mylist.count
LOOP
dbms_output.put_line( mylist(i).ownname||'.'||mylist(i).objname||' '||mylist(i).objtype );
END LOOP;
end;
/
|