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.

How to monitor Long Running Sql statements

This script is to find out long running sql statement in database. It is also returning values 0,1 and 2 to use with Nagios monitoring tools to show normal,warning, critical alerts.


set long 5000
set pages 1000
select  case when max_sql_time is null then 0
    when max_sql_time < 1800 then 0
    when max_sql_time between 1800 and 3600 then 1
    else 2 end
    as return_code, 'OpName is : ' || opname||
    '. The max long running SQL in v$session_longops ran for '|| max_sql_time ||
    ' seconds. Message is ' || message ||
    '. Username is ' || l.username ||
    '. OSuser is ' || sess.osuser||   
    '. Program is '|| sess.program ||
    '. Machine is ' || sess.machine ||
    '. SQL Text is ' || s.sql_fulltext
    as return_statement
    from (select max(elapsed_seconds)
          as max_sql_time,
          username,
          opname,
          message,
          sql_address
          from v$session_longops
          where
           last_update_time > sysdate - interval '15' minute
          and message not like 'RMAN: aggregate input: backup%'
          and message not like 'RMAN: aggregate output: backup%'
          and message not like 'RMAN: incremental datafile backup%'
         group by username, opname, message,sql_address) l,v$sql s,v$session sess
        where l.sql_address=s.address
         AND  sess.sql_address=s.address
       UNION ALL
SELECT 0 as return_code ,to_clob('There are no rows returned') as return_statement   from dual