DBMS_STATS | Objects by size

-----------------------------------------------------------------------------
-- Kamil Piórek -- kamil.piorekk@gmail.com

DECLARE

CURSOR objlst IS


SELECT owner,SEGMENT_name,round((bytes)/(1024*1024)) mb,blocks,SEGMENT_TYPE
  FROM dba_segments
    WHERE segment_type IN ('TABLE','INDEX')
      AND SEGMENT_name NOT LIKE '%$%'
      AND  round((bytes)/(1024*1024)) < 1024 
        ORDER BY 3 DESC   ;

BEGIN
 FOR rec
  IN objlst
    LOOP
      IF rec.segment_type LIKE 'INDEX' THEN
           Dbms_Output.put_line(rec.owner||'.'||rec.segment_name);
              BEGIN DBMS_STATS.GATHER_INDEX_STATS (ownname => rec.owner, indname => rec.segment_name ,estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, DEGREE=> 4  ) ; END ;
      END IF ;
      IF rec.segment_type LIKE 'TABLE' THEN
             Dbms_Output.put_line(rec.owner||'.'||rec.segment_name);
              BEGIN DBMS_STATS.GATHER_TABLE_STATS (ownname => rec.owner, tabname => rec.segment_name ,estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, DEGREE=> 4  ) ; END ;
      END IF ;

    END LOOP  ;
END ;