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

郑全

通过一段动态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，就会报错，所以，我增加了这个限制，避免了运行一段报错。