Re: Maintenance on Indexes

Gillian wrote:

> I need to cut down the fragmentation on the indexes in Financials.
>
> Is there a statement I can use to DROP and CREATE all simultaneously.
> There are about
> 12526 indexes. I could probably use the DBA_IND_COLUMNS table together
>
> with the
> DBA_INDEXES table.
>

Hi Gillian,you can use
alter index owner.index_name rebuild

And you can run the following anonymous pl/sql block to rebuild any index of a user as system. ( if you have problems, give grant alter any index to system ):

set serveroutput on
declare
  cid integer ;
  ret integer ;
  comm varchar2(100) ;
  cursor c_indexes is
   select owner,

          index_name name
   from dba_indexes
   where owner = '--->USERNAME<---';
begin
  for ind in c_indexes loop
    comm :='alter index '||ind.owner||'.'||ind.name||' rebuild' ;     dbms_output.put(comm) ;
    dbms_output.new_line ;
    cid := dbms_sql.open_cursor ;
    dbms_sql.parse(cid,comm ,dbms_sql.v7) ;     ret := dbms_sql.execute(cid) ;
    dbms_sql.close_cursor(cid) ;
  end loop ;
exception
  when others then
    dbms_output.put('some error occured!') ;     dbms_output.new_line ;
end ;
/ Received on Thu Mar 19 1998 - 00:00:00 CST