on 05-04-2007 3:06 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
83 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.