I have created and one can use this simple script.
set serveroutput on
declare
obj_empty dbms_stats.objecttab;
obj_stale dbms_stats.objecttab;
begin
dbms_stats.gather_database_stats(OPTIONS=>'LIST EMPTY',OBJLIST=>obj_empty);
for i in 1 .. obj_empty.count
loop
dbms_output.put_line('Empty statistics--->' ||' Owner: '||obj_empty(i).ownname||'--'||'Object name: '||obj_empty(i).objName||'--'||'Object type: '||obj_empty(i).objType);
end loop;
dbms_stats.gather_database_stats(OPTIONS=>'LIST STALE',OBJLIST=>obj_stale);
for j in 1 .. obj_stale.count
loop
dbms_output.put_line('Stale statistics--->' ||' Owner: '||obj_stale(j).ownname||'--'||'Object name: '||obj_stale(j).objName||'--'||'Object type: '||obj_stale(j).objType);
end loop;
end;
/
Sample output below:
Empty statistics---> Owner: HORIA--Object name: CONTRACTS_SEC--Object type: TABLE
Empty statistics---> Owner: SH--Object name: SYS_IOT_TOP_76625--Object type: INDEX
Empty statistics---> Owner: SH--Object name: SYS_IOT_TOP_76627--Object type: INDEX
Stale statistics---> Owner: SYS--Object name: HISTGRM$--Object type: TABLE
Stale statistics---> Owner: SYS--Object name: HIST_HEAD$--Object type: TABLE
Stale statistics---> Owner: SYS--Object name: IND$--Object type: TABLE
Stale statistics---> Owner: SYS--Object name: MON_MODS$--Object type: TABLE
Nice one !
ReplyDeleteRegis