Sql Script to generate script to reorganize tables to remove fragmentation

This script is shared by Ashfaq Ahmed.


-----------------

Following script will generate script to reorganize tables to remove fragmentation of Scott schema and users tablespace.

declare
cursor c1 is select table_name from dba_tables where tablespace_name='USERS' and owner='SCOTT';
a  number;
d  varchar2(30);
b  number;
begin
 dbms_output.enable(60000);
   open c1;
     b:=0;
     select count(*) into a from dba_tables where tablespace_name='USERS';
     while b<a loop
           fetch c1 into d;
           b:=b+1;
           dbms_output.put_line('alter table '||d||' move tablespace users;');
           declare
             cursor c2 is select index_name from dba_indexes where table_name=d;
             a1    number;
        d1 varchar2(30);
             b1 number;
           begin
             open c2;
               b1:=0;
               select count(*) into a1 from dba_indexes where table_name=d;
               while b1<a1 loop
                  fetch c2 into d1;
                  b1:=b1+1;
                  dbms_output.put_line('alter index '||d1||' rebuild online;');
               end loop;
            end;
       end loop;
end;