Tech & lifestyle

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.

References

1. How to Share Reports by Jeff Smith