Wednesday 4 May 2011

Monitor Table stats in Oracle

This script helps to find out how old the table stats are in oracle database. This will also return value 0 if stats are less than 24 hrs old, 1 if stats are between 24 to 48 hrs and 2 if they are older than 48 hrs.
0 = OK
1= Warning
2= Critical

Select CASE WHEN Time < 24 then 0 when Time between 24 and 48 then 1 else 2 end as return_code,' '||
'Table Name is : ' ||table_name||
'Object Type is : ' ||object_type||
'Time in Hours : ' || Time
as return_statement
from
(Select table_name,object_type,floor(((sysdate - last_analyzed)*24*50*50)/3500) Time
from all_tab_statistics
where owner='SCOTT')
UNION ALL
SELECT 0 as return_code ,'There is no row returned' as return_statement from dual

This script is can be easily modified to monitor stats of whole database or limit to specific objects.




No comments:

Post a Comment