Rebuild Indexs
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Rebuild Indexs

  1. #1
    Join Date
    Sep 2001
    Location
    ENGLAND/UK
    Posts
    1

    Smile

    Hi
    Can any body advice me how can i create simple indexes rebuild scripts on NT or 2000 so I can make it as a batch file to run weekly ???

    Currently i am doing this manually at CMD prompt.

    alter index sapr3."ARFCSSTATE0" rebuild;
    alter index sapr3."ARFCSSTATE~01" rebuild;
    etc....

    I have found this under Oracle Scripts,

    Description:
    This script will rebuild a list of indexes.

    Code:
    select 'alter index '|| index_name || ' rebuild nologging;' from dba_indexes where owner = ' ' --insert database name here


    Would this script apply in my case and how can i use it.
    Thanks

  2. #2
    Join Date
    Feb 2000
    Posts
    175
    Hi,

    Yes this script will produce a line of sql for each index owned by the user identified in the 'owner = ' clause.

    If you send this output to a file eg rebuild.sql - place 'spool rebuild.sql' before this sql then run the rebuild.sql script eg


    set heading off
    set pages 0
    set feed off
    set echo off
    spool rebuild.sql
    select 'alter index '|| index_name || ' rebuild nologging;' from dba_indexes where owner = ' your owners name';
    spool off
    @rebuild

    This will set the environment for the sql (the set cmds) open the file rebuild.sql and insert the select cmds to the file. Close the file and then run the file (@rebuild)

    Hope this helps

    Moff.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width