Re: Maintenance on Indexes

Gillian,

A fairly easy way to do this is to export your database (rows=n is fine and you can use compress=y) and then create an indexfile. If using unix it is normally fairly easy to massage this file. An example follows:

#Create index file from export:

imp / file=expfile.dmp fromuser=ifneeded touser=neededif ignore=y indexes=y indexfile=indexes.sql rows=n

# Remove any REM lines

egrep -v "^REM" indexes.sql > mod_indexes.sql

#To drop the indexes on mass you can create a script using something like:

set pagesize 0
set feedback off
set verify off
set termout off
spool drop_indexes.sql
select 'drop index '||owner||'.'||index_name||';' from dba_indexes
where owner = 'WHATEVER'
/
spool off
exit

# Remember to clean the top and bottom of this file

# or you can insert drop statements before each create in
# the mod_indexes.sql file.  I do not have the time to work
# out the logic for this, but the following example puts a
# commit between each create statement.  It should not
# be to hard to use awk to substitute a drop "index name"

awk < mod_indexes.sql ' /^CREATE / {sub("CREATE","\nCOMMIT; \n\nCREATE")}{print} ' > mod2_indexes.sql

Hope this helps

Doug Carter
Database Administrator
TUI Consulting

Gillian wrote in message <350E980F.6E456F8B_at_entergy.com>...
>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 2526 indexes. I could probably use the DBA_IND_COLUMNS
> table together with the DBA_INDEXES table.
Received on Wed Mar 18 1998 - 00:00:00 CST