Index Maintainer


A simple PL/SQL routine to perform index rebuilds, rebuild-onlines, coalesce with various options. Some of its features are:

Its a PL/SQL procedure, the parameters of which are as follows:

create or replace
procedure index_maintainer(
             p_owner varchar2 default null,
             p_action varchar2 default 'C',
             p_min_size number default 0,
             p_debug number default 0,
             p_degree number default 1,
             p_instance number default 1)

where

p_owner If you only want a single owner to be processed. Default is all owners excepting 'SYS','SYSTEM', 'OUTLN','DBSNMP','TRACESVR', but you can change this trivially in the code
p_debug What kind of output you would like. 0 = just do the work, 1 = do the work and spit out messages for each object processed, 2 = same as 1 but no work is actually done
p_min_size Only indexes larger than this many bytes are processed
p_action C=coalesce, RO=rebuild the index online, R=rebuild the index
p_degree / p_instance This is for parallel executions of the procedure. This uses modulo arithmetic on the object ID to avoid clashes. For example, to run three procedures in parallel you would issue
( print "exec index_maintainer(p_degree=>3, p_instance=>1)" | sqlplus internal ) &
( print "exec index_maintainer(p_degree=>3, p_instance=>2)" | sqlplus internal ) &
( print "exec index_maintainer(p_degree=>3, p_instance=>3)" | sqlplus internal ) &

Source

grant alter any index, analyze any, select any table to ???;

create or replace
procedure index_maintainer(
             p_owner varchar2 default null,   -- if only one owner to be processed
             p_action varchar2 default 'C',   -- C=coalesce,R=rebuild,RO=rebuild online
             p_min_size number default 0,     -- only process indexes of at least this many bytes
             p_debug number default 0,        -- 0=minimal, 1=lots, 2=only messages, no analyze done
             p_degree number default 1,       -- number of anticipated parallel runs
             p_instance number default 1) is  -- which child run this is
  v_start             date   := sysdate;
  v_obj_count         number := 0;
  v_cum_bytes         number := 0;

  v_total_blocks      number;
  v_total_bytes       number;
  v_unused_blocks     number;
  v_unused_bytes      number;
  v_last_file_id      number;
  v_last_block_id     number;
  v_last_block        number; 
  v_owner             varchar2(30) := '*';
  v_sqlcode           number;
  v_partition_clause  varchar2(60);
  v_command           varchar2(255);
  
begin
  if p_instance not between 1 and p_degree then
     raise_application_error(-20000,'Instance must be between 1 and '||p_degree);
  end if;

  if p_degree > 1 then
     dbms_output.put_line('Parallel run: '||p_instance||' of '||p_degree);
   else
     dbms_output.put_line('Serial run');
   end if;

   if upper(p_action) = 'C' then
     dbms_output.put_line('Indexes will be COALESCED');
   elsif upper(p_action) = 'R' then
     dbms_output.put_line('Indexes will be REBUILT');
   elsif upper(p_action) = 'RO' then
     dbms_output.put_line('Indexes will be REBUILT ONLINE');
   else 
     raise_application_error(-20000,'Action must be C, R or RO');
   end if;

   dbms_output.put_line('---------------------');

  dbms_output.enable(999999);
  for i in ( 
     select u.name owner, 
            o.name segment_name, 
            so.object_type segment_type,
            ts.name tablespace_name,
            s.blocks * ts.blocksize bytes,
            o.subname partition_name,
            so.compress_clause,
            decode(o.subname,null,to_char(null),
              decode(upper(p_action),'C',
                'modify partition '||o.subname,
                'partition '||o.subname)) partition_clause
     from sys.user$ u, sys.obj$ o, sys.ts$ ts, sys.seg$ s, sys.file$ f,
          ( select 'INDEX' object_type, 1 object_type_id, 6 segment_type_id, 
                    i.obj# object_id, i.file# header_file, i.block# header_block, 
                    i.ts# ts_number,
                    decode(bitand(i.flags, 32), 32,'COMPRESS '||i.spare2,null) compress_clause
            from sys.ind$ i
            where i.type# in (1, 2, 3, 4, 6, 7, 9)
            union all
            select 'INDEX PARTITION', 20, 6, ip.obj#, ip.file#, ip.block#, ip.ts#,
                   null
            from sys.indpart$ ip
            union all
            select 'INDEX SUBPARTITION', 35, 6, isp.obj#, isp.file#, isp.block#, isp.ts#, null
            from sys.indsubpart$ isp ) so
     where s.file# = so.header_file
       and s.block# = so.header_block
       and s.ts# = so.ts_number
       and s.ts# = ts.ts#
       and o.obj# = so.object_id
       and o.owner# = u.user#
       and s.type# = so.segment_type_id
       and o.type# = so.object_type_id
       and s.ts# = f.ts#
       and s.file# = f.relfile#
       and u.name not in ('SYS',
                      'SYSTEM',
                      'OUTLN',
                      'DBSNMP',
                      'TRACESVR',
                      '...')
      and u.name = nvl(p_owner,u.name)
      and mod(o.obj#,p_degree)+1=p_instance
      order by u.name
  ) loop

      dbms_application_info.set_client_info(i.segment_name||','||i.segment_type||','||i.partition_name);
      dbms_space.unused_space (
         i.owner, 
         i.segment_name,
         rtrim(i.segment_type),
         v_total_blocks,
         v_total_bytes,
         v_unused_blocks,
         v_unused_bytes,
         v_last_file_id,
         v_last_block_id,
         v_last_block,
         i.partition_name);
 
      if p_debug > 0 then 
        dbms_output.put_line(i.segment_type);
        dbms_output.put_line(i.owner||'.'||i.segment_name);
        dbms_output.put_line(v_total_bytes||' bytes allocated');
        dbms_output.put_line((v_total_bytes-v_unused_bytes)||' bytes in use');
        if (v_total_bytes-v_unused_bytes) < p_min_size then
          dbms_output.put_line('Index less then '||p_min_size||' bytes - no action taken');
        end if;
      end if;

      begin
        if (v_total_bytes-v_unused_bytes) > p_min_size then
           if upper(p_action) = 'C' then
                v_command := 'alter index '||i.owner||'.'||i.segment_name||' '||
                             i.partition_clause||' coalesce';
           else
--
-- We alter current session if needed to ensure that we pick up the default tablespace
-- of the index owner, as opposed to that of the proc owner
--
              if upper(p_action) = 'R' then
                    v_command := 'alter index '||i.owner||'.'||i.segment_name||
                                 ' rebuild '||i.partition_clause||' tablespace '||i.tablespace_name||
                                 ' '||i.compress_clause;
              elsif upper(p_action) = 'RO' then
                    v_command := 'alter index '||i.owner||'.'||i.segment_name||
                                ' rebuild '||i.partition_clause||' online tablespace '||i.tablespace_name||
                                ' '||i.compress_clause;
              end if;
           end if;
           if p_debug < 2 then 
              if v_owner != i.owner then
                 execute immediate 'alter session set current_schema = '||i.owner;
                 v_owner := i.owner;
              end if;
              execute immediate v_command;
           end if;
           if p_debug > 0 then
              dbms_output.put_line(v_command);
           end if;
           v_obj_count := v_obj_count + 1;
           v_cum_bytes := v_cum_bytes + v_total_bytes-v_unused_bytes;
        end if;
      exception 
        when others then
          v_sqlcode := sqlcode;
          if v_sqlcode in (-8108,-25176,-8115,-28650) then
              dbms_output.put_line('Ignoring sqlcode '||v_sqlcode);
          else
              raise;
          end if;
      end;
    if p_debug > 0 then 
       dbms_output.put_line('---------------------');
    end if;
  end loop;
  dbms_output.put_line('Indexes Altered : '||v_obj_count);
  dbms_output.put_line('Bytes scanned:    '||v_cum_bytes);
  dbms_output.put_line('Elapsed Time:     '||round((sysdate-v_start)*86400,1));
  commit;
end;
/
show errors