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

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s