SQL QUERY TO FIND OBJECTS(TABLES) WHICH HAS STALE STATS WHICH ARE NOT SYS OR SYSTEM USER

SQL QUERY TO FIND OBJECTS(TABLES) WHICH HAS STALE STATS WHICH ARE NOT SYS OR SYSTEM USER

This query can be used to find stale objects (tables) from dba_objects and dba_tab_statistics.

set lines 200 pages 1000
col OWNER format a20
col OBJECT_NAME format a20
col OBJECT_TYPE format a20
col LAST_ANALYZED format a20
select do.OWNER,
         do.OBJECT_NAME,
         do.OBJECT_TYPE,
         dts.LAST_ANALYZED
  from dba_objects do
  inner join dba_tab_statistics dts on (do.OBJECT_NAME = dts.TABLE_NAME)
  inner join dba_tables dt on (dts.TABLE_NAME = dt.TABLE_NAME)
  where dts.STALE_STATS <> 'NO' 
  and dt.TABLESPACE_NAME = 'SYSTEM'
  and do.OWNER not in ('SYS','SYSTEM')
  order by 2;

Users who dont have analyze dictionary stats in SYSTEM tablespace

set lines 200 pages 1000
col OWNER format a20
col GRANTEE format a20
col PRIVILEGE format a20
select dt.OWNER,
       dsp.GRANTEE,
       dsp.PRIVILEGE,
	   dt.TABLE_NAME,
       dt.TABLESPACE_NAME
  from
  dba_sys_privs dsp
  inner join dba_tables dt on (dsp.GRANTEE = dt.OWNER)
  inner join dba_tab_statistics dts on (dts.OWNER = dt.OWNER)
  where dt.TABLESPACE_NAME = 'SYSTEM'
  and
  dt.owner not in ('SYS','SYSTEM')
  and
  dts.STALE_STATS <> 'NO' 
  and
  dsp.PRIVILEGE <> 'ANALYZE ANY DICTIONARY';

Leave a Reply

%d bloggers like this: