DECLARE
-- The final two lines of this cursor will filter out tables that
   -- have statistics "close" to the real values. You can define how
   -- close by changing the ratio in the second to last line and/or by
   -- changing the block differences in the last line of the cursor. 
   CURSOR c1 IS SELECT t.owner,t.table_name,NVL(t.blocks,0) AS est_blocks,
                   s.blocks,s.bytes,
                   (NVL(t.blocks,0)/s.blocks)*100 AS ratio, 
                   s.blocks-NVL(t.blocks,0) AS diff
                FROM dba_tables t, dba_segments s
                WHERE t.owner=s.owner AND t.table_name=s.segment_name
                AND   NVL(t.blocks,0)/s.blocks < 0.80 
                AND   s.blocks-NVL(t.blocks,0) > 10000;

   tab_owner    DBA_TABLES.OWNER%TYPE;
   tab_name     DBA_TABLES.TABLE_NAME%TYPE;
   tab_est_blks DBA_SEGMENTS.BLOCKS%TYPE;
   tab_blocks   DBA_SEGMENTS.BLOCKS%TYPE;
   tab_bytes    DBA_SEGMENTS.BYTES%TYPE;
   tab_ratio    NUMBER;
   tab_diff     NUMBER;

   mail_sender       VARCHAR2(100) := 'dba@acme.com';
   mail_recipients   VARCHAR2(100) := 'dba@acme.com';
   mail_subject      VARCHAR2(100) := 'ORCL Stats Fix Job';
   mail_message      CLOB;

BEGIN
   mail_message := 'GASQ Stats Fix Job'||CHR(10)||CHR(10);
   mail_message := mail_message || 'The following tables had stats fixed:';
   mail_message := mail_message ||CHR(10)||CHR(10);
   mail_message := mail_message ||RPAD('OWNER.TABLE_NAME',51);
   mail_message := mail_message ||RPAD('EST_BLOCKS',12);
   mail_message := mail_message ||RPAD('BLOCKS',12);
   mail_message := mail_message ||RPAD('RATIO',6);
   mail_message := mail_message ||CHR(10);
   mail_message := mail_message ||RPAD('-',50,'-')||' ';
   mail_message := mail_message ||RPAD('-',11,'-')||' ';
   mail_message := mail_message ||RPAD('-',11,'-')||' ';
   mail_message := mail_message ||RPAD('-',5,'-')||CHR(10);

   OPEN c1;
   LOOP
      FETCH c1 INTO tab_owner,tab_name,tab_est_blks,tab_blocks,
                    tab_bytes,tab_ratio,tab_diff;
      EXIT WHEN c1%NOTFOUND;

      mail_message:= mail_message ||RPAD(tab_owner||'.'||tab_name,50)||' ';
      mail_message:= mail_message ||RPAD(TO_CHAR(tab_est_blks,'99,999,999'),11)||' ';
      mail_message:= mail_message ||RPAD(TO_CHAR(tab_blocks,'99,999,999'),11)||' ';
      mail_message:= mail_message ||LPAD(TO_CHAR(tab_ratio,'990.00'),5);

      -- Redo stats on table. Include all indexed columns and the indexes.
      DBMS_STATS.GATHER_TABLE_STATS ( ownname=>tab_owner,
         tabname => tab_name,
         method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO', 
         cascade => TRUE, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

   END LOOP;

   -- Send report to DBA
   UTL_MAIL.SEND(sender => mail_sender, recipients => mail_recipients,
   subject => mail_subject, message => mail_message);

END;