scn_check9.sql

/*
   SCN internal rate check for Oracle 9.2 and below.
 
     Andrey S. Nikolaev (Andrey.Nikolaev@rdtex.ru)
     http://AndreyNikolaev.wordpress.com
 
        sqlplus /nolog @scn_check9
*/
connect / as sysdba

col date__ format a14
col status format a14
col int_scn_rate format 99999999
/* This statement should work even for noarchivelog 9.2 database */
 
select name dbname,to_char(SYSDATE,'YYYY/MM/DD HH24:MI') DATE_,
int_scn_rate,(case when int_scn_rate > 12*1024 then 'Alert' else 'Normal' end) status
   from v$database,
    (select round(sum(s.value/((sysdate-i.startup_time)*86400))) int_scn_rate
     from gv$sysstat s,gv$instance i where s.inst_id=i.inst_id and s.name='calls to kcmgas');