Helpful SQL Scripts

There may come a time for database developers to perform bulk operations e.g. compile all the stored procedures or rebuild all the indices. This might be required esp. when you are supporting a product and have to upgrade it.

I had to do this with help of oracle XE instance with only a web interface and here is the solution I developed.

Recompiling All Procedures:

begin
declare
cursor c_proc is
select object_name from user_objects where object_type = 'PROCEDURE';
v_sql varchar2(512);

begin
for c_proc_rec in c_proc LOOP
v_sql := 'ALTER PROCEDURE 'c_proc_rec.object_name' COMPILE';
execute immediate v_sql;
END LOOP;

end;
end;

Rebuilding all indexes:
This might be required after inserting thousands of rows as part of product upgrade. Here is the script that I used.

begin
begin
declare
cursor c_idx is
select index_name from user_indexes where index_type = 'NORMAL' and uniqueness = 'NONUNIQUE';
v_sql varchar2(512);

begin
for c_idx_rec in c_idx LOOP
v_sql := 'Alter index 'c_idx_rec.index_name' rebuild';

execute immediate v_sql;
END LOOP;
end;
end;

Hope this helps!