Last analyzed dates in oracle

Description

Lists the last analyze date for tables, indexes and partitions. This script should be used to determine is any stats are out of date. 

The Last_analyzed column being NULL indcates no stats are present

Parameters

None

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

set pages 200

col index_owner form a10
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
/

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
/

PROMPT Regular Indexes

select owner, index_name, last_analyzed, global_stats
from dba_indexes
where owner not in ('SYS','SYSTEM')
order by owner, index_name
/

PROMPT Partitioned Indexes

select index_owner, index_name, partition_name, last_analyzed, global_stats
from dba_ind_partitions
where index_owner not in ('SYS','SYSTEM')
order by index_owner, index_name, partition_name
/

spool off