重庆思庄Oracle、Redhat认证学习论坛

标题: 批量移动表及索引到其他表空间 [打印本页]

作者: 郑全 时间: 2020-3-6 13:24
标题: 批量移动表及索引到其他表空间
通过一段动态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

作者: 郑全 时间: 2020-3-6 13:25
set serveroutput on size 10000000

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

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)