Automating Oracle Oracle Tips by Mike Ault

EXCEPTION
    WHEN ZERO_DIVIDE THEN
         write_out (stat_name,0,4,meas_date,0,is_interactive);
       CLOSE get_count;
    COMMIT;
    WHEN NO_DATA_FOUND THEN
         write_out (stat_name,0,4,meas_date,0,is_interactive);
    COMMIT;
 END;
BEGIN
  stat_name := 'Shared Pool Used';
       SELECT SUM(BYTES)/1048576 INTO stat_val
       FROM  v$sgastat
       SEE CODE DEPOT FOR FULL SCRIPTS
       OPEN get_delta(stat_val,stat_name,comp_date);
       FETCH get_delta INTO delta;
       CLOSE get_delta;
         write_out
(stat_name,stat_val,16,meas_date,delta,is_interactive);
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
         write_out (stat_name,0,16,meas_date,0,is_interactive);
    COMMIT;
 END;
END running_stats;  

---

For more information on this topic, I recommend Don Burleson's latest book "Oracle Tuning: The Definitive Reference".  You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:

Expert Remote DBA BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call BC Remote DBA today.