• From: Mladen Gogala <mgogala@xxxxxxxxxxxxxxxxxxxx>
  • To: oracledbam@xxxxxxxxxxx
  • Date: Wed, 08 Jun 2005 13:13:48 -0400

Seema Singh wrote:

>Does any one has script to findout which indexes need to rebuild in database 
>in single run?
set pagesize 0
set trimout on
set trimspool on
set echo off
spool /tmp/rebuild.sql
select 'alter index '||owner||'.'||index_name||' rebuild;'
from dba_indexes where owner not like '%SYS%';
spool off

This should satisfy even the keenest index rebuilder. Other then that, 
why would you want to rebuild indexes?
I don't think that Oracle indexes need regular rebuilding. Index needs 
to be rebuilt only exceptionally, when
it becomes stale or corrupt. In Oracle9i, even deleted keys can be 
reused, so the argument from Oracle7 and Oracle8
is no longer true. The script above will rebuild your indexes if you 
have CTD.

Mladen Gogala
Oracle DBA
Ext. 121


Other related posts: