Table and Index Rebuild

[bvolf]

Hello,

I have to rebuild about 500 indexes that were created more then 6 months ago.
I have a query:
select *
from all_objects
where trunc(created) < trunc(sysdate - 180)
and object_type like'%INDEX%'
order by owner

How would I automate this?

Thanks in advance,

Boris

[badrinathn]

hi,
creat a script something like this and set up the cron job
#!/bin/sh
#user=${1}
#pass=${2}
#sid=${3}
user="cognos"
pass="focus"
sid=gdmprd
ORACLE_HOME=/app/oracle/product/8.1.7

${ORACLE_HOME}/bin/sqlplus -s ${user}@${sid}/${pass}<<__eof__ > /tmp/am.log 2>&1
spool /tmp/ind.sql
select 'Alter index' || object_name || 'rebuild'
from all_objects
where trunc(created) < trunc(sysdate - 180)
and object_type like'%INDEX%'
order by owner;
spool off
@/tmp/ind.sql
__eof__

[vinit]

Hope this helps.

select 'alter index '||object_name||' rebuild online;'
from all_objects
where trunc(created) < trunc(sysdate - 180)
and object_type like'%INDEX%'
order by owner

Make sure u give the correct tablespace name, sizing parameters.

[Mr.Hanky]

You may want to set a larger sort area size as well...

SQL> ALTER SESSION SET SORT_AREA_SIZE=4000000

This can speed up the sorts dramatically in some cases.

MH