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