Rebuild unusable indexes thru SP
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Rebuild unusable indexes thru SP

  1. #1
    Join Date
    Nov 2002
    Posts
    170

    Rebuild unusable indexes thru SP

    Can I automatically schedule a stored procedure to rebuild my unusable indexes which is happening a lot in my DB since there is direct dataload almost every night. We are using 9i. ANy suggestion ?

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    yes you can

    have a loop to find them and exec immediate to rebuild them

  3. #3
    Join Date
    Nov 2002
    Posts
    170
    Can you please provide me a sample example quick and dirty?

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    you will only learn if you do it you do it yuorself, here is some pseudo code

    Code:
    begin
    for (each index which is unusable) loop
    execute immediate 'rebuild that index';
    end the loop
    end

  5. #5
    Join Date
    Jan 2006
    Posts
    7

    sample

    Here is an example...

    DECLARE
    stmt varchar2(200);
    c1_rec user_indexes%ROWTYPE;

    BEGIN
    FOR c1_rec IN
    (select * from user_indexes where status = 'UNUSABLE')
    LOOP
    stmt := 'ALTER INDEX ' || c1_rec.index_name || ' REBUILD';
    dbms_output.put_line(stmt);
    execute immediate stmt;
    END LOOP;
    dbms_output.put_line('DONE!');
    END;

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