批量移动表及索引到其他表空间

通过一段动态SQL来完成，考虑表及索引，以及大对象和大对象索引的表空间移动

set serveroutput on size 10000000

define tsname=‘SYSMISAPP’

define totsname =‘TSP_SYSMISAPP’

define totsidx=‘TSP_SYSMISAPPIDX’

declare

strtabsql varchar2(200);

stridxsql varchar2(200);

strlobsegsql varchar2(200);

strlobidxsql varchar2(200);

begin

for c_tabresult in (select owner,segment_name,bytes/1024/1024,segment_type

                  from dba_segments

                 where  tablespace_name='&tsname'

                   and segment_type ='TABLE'

                   and bytes/1024/1024<700

                 order by 3 desc) loop

   strtabsql:= 'ALTER table ' || c_tabresult.owner || '.' || c_tabresult.segment_name || ' move tablespace &totsname';

   dbms_output.put_line(strtabsql);

   execute immediate strtabsql;

   for c_idxresult in (

                select owner,index_name

                  from dba_indexes

                 where table_name =c_tabresult.segment_name) loop

       stridxsql:='alter index  ' || c_idxresult.owner || '.' || c_idxresult.index_name || ' rebuild  online tablespace &totsidx';

       dbms_output.put_line(stridxsql);

       execute immediate stridxsql;      

   end loop;

  

   for c_lobsegresult in (

                       select a.owner,b.table_name,b.column_name,b.SEGMENT_NAME

                       from dba_segments a,dba_lobs b

                      where a.segment_name=b.segment_name

                        and a.tablespace_name='&tsname'

                        and b.table_name=c_tabresult.segment_name

                        and a.segment_type='LOBSEGMENT') loop

       strlobsegsql:='ALTER TABLE ' ||  c_lobsegresult.owner || '.' || c_lobsegresult.table_name || ' move tablespace &totsname lob(' || c_lobsegresult.column_name || ') store as ' || c_lobsegresult.SEGMENT_NAME || ' (TABLESPACE &totsname)';

       dbms_output.put_line(strlobsegsql);

       execute immediate strlobsegsql;      

   END loop;

  

   for c_lobidxresult in (

                       select a.owner,b.table_name,b.column_name,b.SEGMENT_NAME

                       from dba_segments a,dba_lobs b

                      where a.segment_name=b.segment_name

                        and a.tablespace_name='&tsname'

                        and b.table_name=c_tabresult.segment_name

                        and a.segment_type='LOBINDEX') loop

       strlobidxsql:='ALTER TABLE ' ||  c_lobidxresult.owner || '.' || c_lobidxresult.table_name || ' move tablespace &totsname lob(' || c_lobidxresult.column_name || ') store as ' || c_lobidxresult.SEGMENT_NAME || ' (TABLESPACE &totsidx)';

       dbms_output.put_line(strlobidxsql);

       execute immediate strlobidxsql;      

   END loop;

end loop;

end;

/

undefine tsname

undefine totsname

undefine totsidx

set serveroutput on size 10000000

注意，这里需要设置 size 10000000,默认为1000，如果DBMS_OUTPUT输出超过1000，就会报错，所以，我增加了这个限制，避免了运行一段报错。