-
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 ?
-
yes you can
have a loop to find them and exec immediate to rebuild them
-
Can you please provide me a sample example quick and dirty?
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|