Oracle's table, index and statistics maintenance...

set serveroutput on size 1000000;

 

DECLARE

  v_current_table   INTEGER;

  v_current_index   INTEGER;

  v_table_count     INTEGER;

  v_index_count     INTEGER;

  v_exception_count INTEGER;

BEGIN

  v_current_table   := 1;

  v_exception_count := 0;

 

  SELECT COUNT(*)

    INTO v_table_count

    FROM all_all_tables aat

   WHERE aat.owner = 'SRQF1'

     AND aat.num_rows IS NOT NULL

   ORDER BY aat.num_rows;

 

  FOR t IN (

            SELECT *

              FROM all_all_tables aat

             WHERE aat.owner = 'SRQF1'

               AND aat.num_rows IS NOT NULL

             ORDER BY aat.num_rows

           )

  LOOP

    dbms_output.put_line('Processing table ' || t.owner || '.' || t.table_name || ' - ' || v_current_table ||

                         ' of ' || v_table_count || '...');

 

    BEGIN

      EXECUTE IMMEDIATE 'ALTER TABLE ' || t.owner || '.' || t.table_name || ' ENABLE ROW MOVEMENT';

      EXECUTE IMMEDIATE 'ALTER TABLE ' || t.owner || '.' || t.table_name || ' SHRINK SPACE CASCADE';

      EXECUTE IMMEDIATE 'ALTER TABLE ' || t.owner || '.' || t.table_name || ' DISABLE ROW MOVEMENT';

    EXCEPTION

      WHEN OTHERS THEN

        v_exception_count := v_exception_count + 1;

        BEGIN

          EXECUTE IMMEDIATE 'ALTER TABLE ' || t.owner || '.' || t.table_name || ' DISABLE ROW MOVEMENT';

        EXCEPTION

          WHEN OTHERS THEN

            v_exception_count := v_exception_count + 1;

        END;

        dbms_output.put_line('    Failed to shrink space cascade. Skipping...');

    END;

 

    v_current_index := 1;

 

    SELECT COUNT(*)

      INTO v_index_count

      FROM all_indexes ai

     WHERE ai.table_owner = t.owner

       AND ai.table_name = t.table_name

       AND ai.num_rows IS NOT NULL

     ORDER BY ai.num_rows;

 

    FOR i IN (

              SELECT *

                FROM all_indexes ai

               WHERE ai.table_owner = t.owner

                 AND ai.table_name = t.table_name

                 AND ai.num_rows IS NOT NULL

               ORDER BY ai.num_rows

             )

    LOOP

      dbms_output.put_line('    Processing index ' || i.owner || '.' || i.index_name || ' - ' || v_current_index ||

                           ' of ' || v_index_count || '...');

   

      BEGIN

        EXECUTE IMMEDIATE 'ALTER INDEX ' || i.owner || '.' || i.index_name || ' REBUILD';

      EXCEPTION

        WHEN OTHERS THEN

          v_exception_count := v_exception_count + 1;

          dbms_output.put_line('        Failed to index rebuild. Skipping...');

      END;

   

      v_current_index := v_current_index + 1;

    END LOOP;

 

    dbms_output.put_line('    Gathering table stats...');

    BEGIN

      dbms_stats.gather_table_stats(t.owner, t.table_name);

    EXCEPTION

      WHEN OTHERS THEN

        v_exception_count := v_exception_count + 1;

        dbms_output.put_line('        Failed to gather stats. Skipping...');

    END;

 

    v_current_table := v_current_table + 1;

  END LOOP;

END;

/

 

set serveroutput off;