cancel
Showing results for 
Search instead for 
Did you mean: 

Index rebuild script help

Former Member
0 Kudos

Hi All,

I am trying to build a simple oracle index rebuild script, which basically runs and rebuilds specified indexes.

We are running Oracle database on HPUX enviroment.

No so far i have only manged to this

I created 2 scripts

file 1 : rebuild

#!/bin/csh

sqlplus system/manager @ indexrebuild.sql

file 2 :indexrebuild.sql

sqlplus '/as sysdba'

spool /tmp/rebuildlog

alter index SAPR3."IHPA~A" rebuild online;

spool off

exit

I ran the script, which seems to work perfectly. I only get one error message. Any idea what is causingthis error. thanks

bhlukd01:orad01 28> ./rebuild

SQL*Plus: Release 9.2.0.7.0 - Production on Fri May 4 14:57:46 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production

With the Partitioning option

JServer Release 9.2.0.7.0 - Production

SP2-0734: unknown command beginning "sqlplus '/..." - rest of line ignored.

Index altered.

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production

With the Partitioning option

JServer Release 9.2.0.7.0 - Production

bhlukd01:orad01 29>

I need help with commands and syntax.

I want cron this script to run every morning ONLINE, which rebuilds specified indexes and then produces log /tmp/rebuildlog.

Can anyone help me or point me right direction to prepare this simple script. my scripting knowledge is very basic.

thanks

Accepted Solutions (0)

Answers (1)

Answers (1)

lbreddemann
Active Contributor
0 Kudos

Hi Rizwan,

the problem with your script:


sqlplus '/as sysdba'
spool /tmp/rebuildlog
alter index SAPR3."IHPA~A" rebuild online;
spool off
exit 

is the first line.

In sqlplus, you've to use <b>conn</b> to connect to a database.

If you change this, also change the call of your script to

sqlplus /nolog @indexrebuild.sql

.

There is no need to connect as SYSTEM user first (and thus show all the world that your system user still has the default password... ;-).

As you're using Oracle 9.2i the optimizer statistics for the newly created indexes are not automatically recreated. Therefore I would recommend you'll have brconnect run afterwards, so that the index stats are in place.

Ok, that's about your question.

More interesting is what is the background of this script.

Why do you want to rebuild indexes like that? Regularly ??!

Rethink your plan, as there are only few occurrences when rebuilding an index should be applied.

You may want to see note

<a href="http://service.sap.com/sap/support/notes/771929">#771929 - FAQ: Index fragmentation</a>

before scheduling your script...

Having the indexes rebuild every day will not solve any problem.

If you observe that the index keeps getting bigger and bigger - that's for a reason.

Perhaps it's just that the index "likes" to be that fat (meaning that the "compressed" form after the rebuild does not support updates and inserts very well).

Perhaps the storage parameters of the index/tablespace are not optimal.

Do yourself a favor: check the root cause for your index problem, fix it and then: leave it alone.

There's just no need for what you're about to do.

In case you don't get to the root cause: open a support message. We will help you with this.

KR Lars