移动表到指定表空间的动态sql

郑全

很多时候，我们想把一个表空间的数据移动到另一个表空间去，如果只是一张表，那比较简单，但如果有很多张表，数据比较大，比如有几t，那么，这时使用单纯的一条条手工命令，可能容易出现问题，毕竟移动表空间，这里涉及到索引的创建，lobsegment,lobindex的类型的移动，这些都一条条去输入，容易出错，下面我写了一个脚本，使用动态 sql来完成这个操作：
比如要把users表空间的数据移动到ts_exchange表空间去，那么就修改前两行即可，反之，调换一下即可。

set serveroutput on
define tsname='USERS'
define totsname ='TS_EXCHANGE'
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'
                    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 tablespace_name='&tsname'
                      and table_name =c_tabresult.segment_name) loop
          stridxsql:='alter index  ' || c_idxresult.owner || '.' || c_idxresult.index_name || ' rebuild  online tablespace &totsname';
          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 &totsname)';
          dbms_output.put_line(strlobidxsql);
          execute immediate strlobidxsql;      
      END loop;
      
  end loop;
end;
/
undefine tsname
undefine totsname

郑全

这种方式的最大好处，就是一张张表移动，对于受影响的面，可控。