One of the often asked questions is to find out the status of the statistics of the application owned schemas. In this article, I will list the SQL code that I used for this purpose. As well, I will publish a SQL Developer report that can be used for the same purpose.
select owner , table_name , PARTITION_NAME , subpartition_name , LAST_ANALYZED , stale_stats , STATTYPE_LOCKED from dba_tab_statistics where owner not in -- non-application schemas or other excluded shemas ('SYS' , 'SYSTEM' , 'AUDSYS' , 'APPQOSSYS' , 'CTXSYS' , 'DBSNMP' , 'XDB' , 'GSMADMIN_INTERNAL' , 'OUTLN' , 'WMSYS' , 'ORDDATA' , 'ORDSYS' , 'MDSYS' , 'LBACSYS' , 'DVSYS' , 'OJVMSYS' , 'DBSFWUSER' , 'OLAPSYS' , 'SQLTXPLAIN' ) and table_name not in -- tables to be excluded explicitly ( 'PARTITION_LIST' , 'SUBPAR_LIST' , 'TAB_STAT_LIST' ) and ( stale_stats = 'YES' or stale_stats is null ) and (owner , table_name) not in ( select owner, table_name from dba_tables where temporary = 'Y' ) order by 1, 2, 3, 4;
For ease of use, I have embedded the same into a SQL Developer report that can be downloaded here.
- How to Share Reports by Jeff Smith