Many databases are very heavy on the indexes and checking the depth and number of deleted leafs on everyone is just not practical in a single job. I have a PL/SQL jobs that runs for "N" minutes during low activity periods every night against the indexes. It stores that last object id of the index last checked so the next time it runs it will pick up where it left off. I either put the SQL to rebuild the indexes in a table, run it then or write it out to a file. This can then be run during a maintenance window or while the db is operation depending on the version of Oracle. Once you have rebuilt all the suspect indexes you can start target indexes are associated with high activity tables. These can be identified by activating table monitoring and using DBA_TAB_MODIFICATIONS. This can all pretty much be automated.
- Ethan -----Original Message----- Sent: Wednesday, March 13, 2002 3:25 PM To: Multiple recipients of list ORACLE-L Steve Adams's site http://www.ixora.com.au has scripts which help with this. Also your indexes many only need coalescing. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -----Original Message----- Sent: Wednesday, March 13, 2002 12:52 PM To: Multiple recipients of list ORACLE-L Hi, I want to automate index rebuilds on some databases I administer. They are all Oracle 8.1.6 and 8.1.7 on Solaris 2.6 thru 2.8. I want to use "analyze index...validate structure" and then look at the index_stats view to determine what shape a given index is in. My question... for people who use this method, what selection criteria do you use on the index_stats view to make the decision to rebuild... or not to rebuild the particular index??? My aim would be to use such a selection criteria and limit the rebuilds to just a few each night... until at some point they are all done. After that I would run say once a week just to pick on indexes as they become fragmented and skewed etc etc. Thanks, Ken -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
