--------------------------------------------------------------------------------
--  what : rbldindx.sql
--  when : 20-APR-2006
--   why : script for detecting bloated indexes and rebuilding them
-- where : DB optimization effort - TNG
--   how : First thing is to declare a local function. This accepts one argument
--         as the name of an index to analyze. The result of index validation is
--         found in the one-line temporary view, INDEX_STATS, so the function
--         accesses that view to return the percentage of its allocated storage 
--         that the index actually uses. Next thing is to get the names of 
--         relevant indexes (owned by the user we want to analyze, having more 
--         than one extent of storage space allocated, and so forth) in a cursor.
--         Then loop through the cursor. For each index, check its percentage of
--         space used. If the percentage is less than 75, rebuild it and call 
--         the local function again so we can print a few lines to let the DBA
--         know if the rebuild helped.
--   who : David Clement, SUM Global Technology
--         Copyright DCSI 2006 - All rights reserved
--   fix :
---------------------------------------------------------------------------------
declare 
	u number := 0;
	function get_pct_used (obj in varchar2)
	return number
	is
		v number := 0;
	begin
		execute immediate 'analyze index '||obj||' validate structure';
		select pct_used 
		into v
		from index_stats;
		return v;
	end get_pct_used;
begin
	for idx in (select o.object_name
		from dba_objects o
                join dba_segments s
                on (o.object_name = s.segment_name
			and o.object_type = s.segment_type
			and o.owner = s.owner)
		where o.object_type = 'INDEX'
		and o.owner = '&1'
		and o.generated = 'N'
                and o.object_name not like 'BIN$%'
		and s.extents > 1)
	loop
		u := get_u (idx.object_name);
                if (u < 75) then
			dbms_output.put_line (idx.object_name||' has '||to_char(u)||' percent used');
			dbms_output.put_line ('Rebuilding');
                        execute immediate 'alter index '||idx.object_name||' rebuild';
			u := get_u (idx.object_name);
			dbms_output.put_line (idx.object_name||' now has '||to_char(u)||' percent used');
		end if;
	end loop;
end;