Oracle Database

Automatic Shrink

The company I work for would rebuild their tables and indexes every quarter, this did cause some concern for me and they were adamant that this needed to be done. I compromised and came up with this script.

create or replace PROCEDURE cad_maintenance (
    debugme IN VARCHAR2 DEFAULT 'true'
) IS
/*
* 
*  Author:             Mark Young. 
*  Date:               22-November-2109
*  Purpose:            It will put freespace back on the list to be reused.. 
*  History:            22/11 - V1.0 - Original version of the code
*                      28/11 - v1.1 - Changed from all tables and all indexes, just action only the required objects.
*                      02/12  -v1.2 - Bug fixes and testing complete     

* Requirements:        This script requires the following:
*                      grant select on dba_tab_statistics to CAD;
*                      grant execute on dbms_space to CAD;
*                      grant select on dba_tab_statistics to CAD;
*                      grant execute on dbms_stats to CAD;  
*                      grant select any dictionary to CAD;
*                      grant execute on sys.utl_file to CAD; 
*                      grant analyze any to CAD;  
*/

    fhandle       utl_file.file_type;
    max_linesize  number := 32000; 
    f_date        DATE;
    c_date        varchar2(50);
    v_count       integer;
    v_str         varchar2(255);
    t1            integer;
    s_date        varchar2(50);
    tenm          number := 10475520;
    v_saved       varchar2(15);
    
   --Define our cursor and lets see what objects are a candidate for the required action.
    CURSOR c_objects IS
    SELECT segment_name,
      decode(c3,'', NULL, 'X'),replace(c3,chr(34)) AS action1,
      decode(c2,'', NULL, 'X'),replace(c2,chr(34)) AS action2,
      decode(c1,'', NULL, 'X'),replace(c1,chr(34)) AS action3,
      reclaimable_space,
      recommendations
    FROM
        TABLE ( dbms_space.asa_recommendations('FALSE', 'TRUE', 'TRUE') ) --All Runs, Show Manual, Show Findings 
    WHERE SEGMENT_OWNER='CAD';

BEGIN
    --Get the current time so we can calculate how long this procedure takes to run.
    t1 := dbms_utility.get_time;
    --Lets open the cursor and see what qualifies
   SELECT to_char(sysdate,'ddmmyyyyhh24miss'),to_char(sysdate,'dd/mm/yyyy HH24:mi:ss') 
                INTO c_date, s_date
                FROM dual;
                
  dbms_output.put_line('open file');
  --Make sure you place the correct directory in the line below;
  --select * from dba_directories; 
  fhandle := utl_file.fopen('MYDIR', c_date || '_table_index_colesce.txt', 'W', max_linesize);
  utl_file.put (fhandle, 'Starting at ' || s_date || chr(10)); 
  utl_file.put (fhandle, 'If there are any objects to process.. here is where we will see them' || chr(10)); 
   
 FOR getobjects IN c_objects
    LOOP
        
            --Lets see what action is required and report on the benefits
        
            IF debugme = 'true' THEN
  
                dbms_output.put_line(getobjects.action1  || ' - ' || getobjects.action2 || ' - ' || getobjects.action3);
                dbms_output.put_line('writing to file');

                if getobjects.reclaimable_space < tenm then
                    v_saved := ' Not saved ';
                else
                    v_saved := ' Saved ';
                end if;
                
                utl_file.put ( fhandle, getobjects.segment_name || ' - ' || getobjects.recommendations || ' - ' ||  v_saved  || getobjects.reclaimable_space || chr(10));
                
            ELSE
                if getobjects.reclaimable_space < tenm then
                    v_saved := ' Not saved ';
                else
                    v_saved := ' Saved ';
                end if;
                
                dbms_output.put_line('Output to table_index_colesce for statistics');
                utl_file.put (fhandle, 'Output to table_index_colesce for statistics ' || chr(10)); 
                utl_file.put ( fhandle, getobjects.segment_name || ' - ' || getobjects.recommendations || ' - ' ||  v_saved  || getobjects.reclaimable_space || chr(10));
                                            
                if getobjects.action1 <> 'X' then
                   utl_file.put (fhandle, 'executing... ' || getobjects.action1 || chr(10)); 
                   execute immediate  getobjects.action1;
                end if;
                                            
                if getobjects.action2 <> 'X' then
                    utl_file.put (fhandle, 'executing... ' || getobjects.action2 || chr(10)); 
                    dbms_output.put_line( getobjects.action2);
                    execute immediate  getobjects.action2;
                end if;
                                            
                if getobjects.action3 <> 'X' then
                    utl_file.put (fhandle, 'executing... ' || getobjects.action3 || chr(10)); 
                    execute immediate getobjects.action3;
                end if;
            END IF;

END LOOP;
    
      /* ------ Statistics --------------- */
      dbms_output.put_line('Starting to collect statistics');
      --Now we need to capture the statistics before they are updated, this can be reverted back if necessary
      select count(*) into v_count from dba_objects where object_name='CAD_STATS_TABLE';
      IF v_count = 0 THEN
        
        v_str := 'begin dbms_stats.CREATE_STAT_TABLE( ownname=>user, stattab=>''CAD_STATS_TABLE''); end;';
        if debugme = 'true' then
          dbms_output.put_line(v_str);
        else
          execute immediate v_str;
        end if;
      END IF;

      --Take a copy of the statistics (this is important, otherwise you have nothing to go back to
      v_str := 'begin dbms_stats.export_schema_stats( ownname=>user, stattab=>''CAD_STATS_TABLE'', statid=>''CURRENT_STATS''); end;';
      if debugme = 'true' then
          dbms_output.put_line(v_str);
      else
          execute immediate v_str;
      end if;

  
   
    FOR stalestats in (SELECT  table_name  FROM  dba_tab_statistics WHERE stale_stats = 'YES' and STATTYPE_LOCKED='ALL' AND owner='CAD')
    LOOP
        utl_file.put (fhandle, 'Unlock Statistics' || chr(10));
        v_str := 'begin dbms_stats.unlock_table_stats(''CAD'',''' || stalestats.table_name || ''' ); end;';
        if debugme = 'true' then
            --unlock the table statistics
            dbms_output.put_line (v_str);
        else
            execute immediate v_str;
        end if;
    END LOOP;

      --gather the table statistics across the schema, this is more efficient
       utl_file.put (fhandle, 'Gather Statistics' || chr(10));
      v_str := 'begin dbms_stats.gather_schema_stats(ownname=>''CAD'',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,degree =>4); end;';
      if debugme = 'true' then
            dbms_output.put_line(v_str);
      else
            execute immediate v_str;
      end if;

      --Finally need to lock the statistics for another time
      utl_file.put (fhandle, 'Lock Statistics' || chr(10));
      FOR lock_stats in (SELECT  table_name  FROM  dba_tab_statistics WHERE owner='CAD')
      LOOP 
      v_str := 'begin dbms_stats.lock_table_stats(''CAD'',''' || lock_stats.table_name || '''); end;';
      IF debugme = 'true' THEN
        --lock the table statistics
        dbms_output.put_line(v_str);
      ELSE
        EXECUTE IMMEDIATE v_str;
    END IF;
END LOOP;


dbms_output.put_line('Closing open file');
utl_file.put (fhandle, 'Completed - ' || (dbms_utility.get_time - t1)/100 || ' seconds' || chr(13));
utl_file.fclose(fhandle);


EXCEPTION
    WHEN no_data_found THEN
        --utl_file.fclose(fhandle);
        NULL;
    WHEN OTHERS THEN
        raise_application_error(-20001, 'An error was encountered - ' || sqlcode || ' -ERROR- ' || sqlerrm);
        utl_file.fclose(fhandle);       
END;
Print Friendly, PDF & Email

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.