set serveroutput on size 999999
undefine owner
declare
mylist dbms_stats.ObjectTab;
begin
-- get list of missing stats
dbms_output.put_line('EMPTY');
dbms_output.put_line('=====');
-- dbms_stats.gather_schema_stats(ownname=>user,options=>'LIST EMPTY',objlist=>mylist);
dbms_stats.gather_database_stats(options=>'LIST EMPTY',objlist=>mylist);
FOR i IN 1..mylist.count
LOOP
dbms_output.put_line(mylist(i).ownname||'.'||mylist(i).objname||' '||mylist(i).partname||' '||mylist(i).objtype);
END LOOP;
-- get list of stale stats
dbms_output.put_line('');
dbms_output.put_line('STALE');
dbms_output.put_line('=====');
-- dbms_stats.gather_schema_stats(ownname=>user,options=>'LIST STALE',objlist=>mylist);
dbms_stats.gather_database_stats(options=>'LIST STALE',objlist=>mylist);
FOR i IN 1..mylist.count
LOOP
dbms_output.put_line(mylist(i).ownname||'.'||mylist(i).objname||' '||mylist(i).partname||' '||mylist(i).objtype);
END LOOP;
end;
/
