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

最新推荐文章于 2021-09-02 09:35:04 发布

转载最新推荐文章于 2021-09-02 09:35:04 发布 · 272 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/u/3761438/blog/2054728

文章标签：

#数据库 #python

本文提供了一种使用动态SQL批量迁移Oracle表空间的方法。通过一个脚本实现了表及其索引、LOB段等元素从一个表空间到另一个表空间的迁移，适用于大规模数据迁移场景。

2019独角兽企业重金招聘Python工程师标准>>>

image.php?url=0K4K15BQls 很多时候，我们想把一个表空间的数据移动到另一个表空间去，如果只是一张表，那比较简单，但如果有很多张表，数据比较大，比如有几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 || ' rebuildonline 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

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