Here is a code snipet that allows you to check which indexes if you
would rather. To execute it you need to use the analyze..validate
clause. It populates index_stats, but only information from one index
at a time is there.

for r_indx in c_indx loop
begin
DBMS_SQL.PARSE(c_name,'analyze index ' || r_indx.owner || '.' ||
r_indx.index_name || ' validate
structure',DBMS_SQL.NATIVE);

select HEIGHT, decode (LF_ROWS,0,1,LF_ROWS), DEL_LF_ROWS,
decode (DISTINCT_KEYS,0,1,DISTINCT_KEYS)
into height, lf_rows, del_lf_rows, distinct_keys
from index_stats;

-- Index is considered as candidate for rebuild when :
-- - when deleted entries represent 20% or more of the current
entries
-- - when the index depth is more then 4 levels.(height starts
counting from 1 so > 5)
-- Index is (possible) candidate for a bitmap index when :
-- - distinctiveness is more than 99%

if ( height > 5 ) OR ( (del_lf_rows/lf_rows) > 0.2 ) then
dbms_output.put_line (rpad(r_indx.owner,16,' ') ||
rpad(r_indx.index_name,40,' ') ||

lpad(round((del_lf_rows/lf_rows)*100,3),17,' ') ||
lpad(height-1,7,' ') ||
lpad(round((lf_rows-distinct_keys)*100/lf_rows,3),16,' '));


DBMS_SQL.PARSE(c_name,'alter index ' || r_indx.owner || '.' ||
r_indx.index_name || ' REBUILD COMPUTE
STATISTICS LOGGING',DBMS_SQL.NATIVE);

end if;

EXCEPTION
WHEN OTHERS
THEN
EMSG := SQLERRM;
DBMS_output.put_line( 'alter index ' || r_indx.owner || '.' ||
r_indx.index_name ||
...


-----Original Message-----
From: Anthony Ballo
[mailto:oracledba-ezmlmshield-x97129512.[Email address protected]
Sent: Wednesday, June 29, 2005 12:42 PM
To: LazyDBA Discussion
Subject: RE: Rebuild Indexes

rem ----------------Rebuild Script
Start---------------------------------
rem
-----------------------------------------------------------------------
rem Filename: idxrebld.sql
rem Purpose: Re-build all user indexes on-line
rem Notes: - Run this script from sqlplus as a DBA user.
rem
-----------------------------------------------------------------------

set serveroutput on size 1000000
set line 1024 feed off trimspool on echo off
spool H:\sql\indx\index_rebuild_run.sql

begin
for c1 in (
select i.owner, i.index_name, s.tablespace_name,
i.initial_extent, i.next_extent, i.min_extents, i.max_extents,
i.pct_increase, s.bytes
from sys.dba_segments s, sys.dba_indexes i
where s.segment_type = 'INDEX'
and i.index_name = s.segment_name
and i.index_name between 'A%' and 'Z%'
and i.owner = 'YOUR_SCHEMA' order by i.index_name )
loop

dbms_output.put_line('ALTER INDEX "'||c1.owner||'"."'||c1.index_name||
'" REBUILD ONLINE;');

end loop;
end;
/

spool off

PAUSE

-- Run the generated script
set echo on time on timing on feed on
@H:\sql\indx\index_rebuild_run
spool off

exit
rem ----------------Rebuild Script End---------------------------------





-----Original Message-----
From: George
[mailto:oracledba-ezmlmshield-x11158895.[Email address protected]
Sent: Monday, June 27, 2005 9:28 PM
To: LazyDBA Discussion
Subject: Rebuild Indexes

Can any body having scripts which will show what are the indexes need to
be rebuild ?



Thanks in advance

George





DISCLAIMER:
This message contains privileged and confidential information and is
intended only for the individual named.If you are not the intended
recipient you should not disseminate,distribute,store,print, copy or
deliver this message.Please notify the sender immediately by e-mail if
you have received this e-mail by mistake and delete this e-mail from
your system.E-mail transmission cannot be guaranteed to be secure or
error-free as information could be
intercepted,corrupted,lost,destroyed,arrive late or incomplete or
contain viruses.The sender therefore does not accept liability for any
errors or omissions in the contents of this message which arise as a
result of e-mail transmission. If verification is required please
request a hard-copy version.


--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com To unsubscribe:
see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com By using this list you agree
to these terms:http://www.lazydba.com/legal.html



--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html



Oracle LazyDBA home page