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';