Pour afficher les stats sur les tables :
set pages 200
col index_owner form a10
col TABLE_NAME for a40
col table_owner form a10
col owner form a10
spool checkstat.lst
PROMPT Regular Tables
select owner,table_name,last_analyzed, global_stats
from dba_tables
where owner not in ('SYS','SYSTEM')
order by owner,table_name
/
Pour les tables avec partitions :
set pages 200
col index_owner form a10
col TABLE_NAME for a40
col table_owner form a10
col owner form a10
spool checkstat.lst
PROMPT Partitioned Tables
select table_owner, table_name, partition_name, last_analyzed, global_stats
from dba_tab_partitions
where table_owner not in ('SYS','SYSTEM')
order by table_owner,table_name, partition_name
/
Pour afficher les stats sur les indexes ;
set pages 500
set lines 200
col index_owner form a10
col index_name for a40
col table_owner form a10
col owner form a10
select owner, index_name, last_analyzed, global_stats
from dba_indexes
where owner not in ('SYS','SYSTEM')
order by 3
/
Pour afficher les stats manquant avec un taux de % de 10 :
select m.TABLE_OWNER,
m.TABLE_NAME,
m.INSERTS,
m.UPDATES,
m.DELETES,
m.TRUNCATED,
m.TIMESTAMP as LAST_MODIFIED,
round((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0),2) as EST_PCT_MODIFIED,
t.num_rows as last_known_rows_number,
t.last_analyzed
From dba_tab_modifications m,
dba_tables t
where m.table_owner=t.owner
and m.table_name=t.table_name
and table_owner not in ('SYS','SYSTEM')
and ((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0) > 10 or t.last_analyzed is null)
order by timestamp desc;