Script for Moving Tables / Indexes to A Different Tablespace－DanBrother的部落格

/************************************************************/
/* Script for moving tables into a different tablespace      */
/*  Note: After executing the script, relative indexes          */
/*  will be invalid and need to be rebuilt ;                                 */
/* SPs need to be recompiled.                                                            */
/* == Author: Daniel Chung                                                             */
/************************************************************/

set serveroutput on size 1000000
declare
v_from_tb constant varchar2(30):='USERS'; -- From Tablespace
v_to_tb constant varchar2(30):='USERS2';    -- To Tablespace
script varchar2(1024) default null;
begin
for rec in (select segment_name as name,segment_type as type
            from user_segments
            where segment_type = 'TABLE' and tablespace_name = v_from_tb
           ) loop
   begin
       script:='alter table '||rec.name||' move tablespace ' ||v_to_tb;
       execute immediate script;
       dbms_output.put_line(script);
    exception
       when others then
            dbms_output.put_line(rec.name||'=>'||sqlerrm);
   end;
end loop;
end;
/

/************************************************************/
/* Script for moving indexes into a different tablespace */
/* Author: Daniel Chung */
/************************************************************/

set serveroutput on size 1000000
declare
v_from_tb constant varchar2(30):='INDX3'; -- From Tablespace
v_to_tb constant varchar2(30):='INDX'; -- To Tablespace
script varchar2(1024) default null;
v_col_name varchar2(1000) default null;
v_object_id number;
v_ct number(3) default 0;
v_default$ long default null;

begin
for rec in (select owner,table_name,index_name as name,index_type as type
            from dba_indexes
            where tablespace_name = v_from_tb
           ) loop
   begin
    if rec.type = 'NORMAL' then -- Normal Indexes
       script:='alter index '||rec.owner||'.'||rec.name||' rebuild online tablespace '||v_to_tb;
       execute immediate script;
       dbms_output.put_line(script);
    elsif (rec.type like '%BITMAP%') or (rec.type like 'FUNCTION-BASED%') then
       v_col_name:='';
       v_ct:=0;
       for in_rec in (select column_name
                      from dba_ind_columns
                      where index_name = rec.name
                     ) loop
        if rec.type in ('FUNCTION-BASED BITMAP','FUNCTION-BASED NORMAL') then
           select b.default$
           into v_default$
           from dba_objects a,sys.col$ b
           where a.object_name = rec.table_name and a.object_id = b.obj#
           and b.name = in_rec.column_name;

           v_col_name:=v_col_name||v_default$;
           v_ct:=v_ct+1;
        else
           v_col_name:=v_col_name||in_rec.column_name;
        end if;
      end loop;

      if v_ct > 1 then
         v_col_name:=replace(v_col_name,')',',');
         v_col_name:=v_col_name||')';
      end if;

       script:='drop index '||rec.owner||'.'||rec.name;
       execute immediate script;
       dbms_output.put_line(script);

       if rec.type like '%BITMAP%' then
          script:='create bitmap index '||rec.name||' on '||rec.table_name||'('||v_col_name||') tablespace '||v_to_tb;
       else
          script:='create index '||rec.name||' on '||rec.table_name||'('||v_col_name||') tablespace '||v_to_tb;
       end if;
       execute immediate script;
       dbms_output.put_line(script);
    end if;

    exception
       when others then
            dbms_output.put_line(rec.name||'=>'||sqlerrm);
   end;
end loop;
end;
/