oracle 分区表换表空间,sql – 如何在Oracle 11g中将分区表从一个表空间移动到另一个表空间？...

您必须考虑可能无效的索引 – 为了涵盖关于重置默认表空间的问题,我认为这是您要实现的完整过程：

1)移动分区(根据zürigschnäzlets答案的PL / SQL循环)

这些是我在匿名块包装器中使用的程序,它定义了a_tname,a_destTS,vTname和vTspName – 他们应该给出一般的想法：

procedure mvTabPart (a_tname in varchar2, a_destTS in varchar2) is

cursor pCur(vTname varchar2, vTspName varchar2) is

select table_name, partition_name

from user_tab_partitions

where table_name = vTname

and tablespace_name not like vTspName

order by partition_position desc;

begin

for pRow in pCur(a_tname, a_destTS) loop

sqlStmnt := 'alter table '||pRow.table_name||

' move partition '||pRow.partition_name||

' tablespace '||a_destTS;

execute immediate sqlStmnt;

end loop;

end mvTabPart;

2)设置表默认分区表空间,从而创建新的分区：

procedure setDefTabPart (a_tname in varchar2, a_destTS in varchar2) is

cursor tCur(vTname varchar2) is

select table_name

from user_part_tables

where table_name = vTname;

begin

for tRow in tCur(a_tname) loop

sqlStmnt := 'alter table '||tRow.table_name||

' modify default attributes '||

' tablespace '||a_destTS;

execute immediate sqlStmnt;

end loop;

end setDefNdxPart;

3)设置索引默认分区表空间,以便创建新的索引分区(如果有的话)：

procedure setDefNdxPart (a_tname in varchar2, a_destTS in varchar2) is

cursor iCur(vTname varchar2) is

select index_name

from user_part_indexes

where index_name in (select index_name

from user_indexes where table_name = vTname);

begin

for iRow in iCur(a_tname) loop

sqlStmnt := 'alter index '||iRow.index_name||

' modify default attributes '||

' tablespace '||a_destTS;

execute immediate sqlStmnt;

end loop;

end setDefNdxPart;

4)重建任何需要重建并且不在所需表空间中的分区索引：

procedure mvNdxPart (a_tname in varchar2, a_destTS in varchar2) is

cursor ndxCur(vTname varchar2, vTspName varchar2) is

select i.index_name index_name, ip.partition_name partition_name

from user_ind_partitions ip, user_indexes i

where i.index_name = ip.index_name

and i.table_name = vTname

and i.partitioned = 'YES'

and (ip.tablespace_name not like vTspName or ip.status not like 'USABLE')

order by index_name, partition_name ;

begin

for ndxRow in ndxCur(a_tname, a_destTS) loop

sqlStmnt := 'alter index '||ndxRow.index_name||

' rebuild partition '||ndxRow.partition_name||

' tablespace '||a_destTS;

execute immediate sqlStmnt ;

end loop;

end mvNdxPart;

5)重建任何全局索引

procedure mvNdx (a_tname in varchar2, a_destTS in varchar2) is

cursor ndxCur(vTname varchar2, vTspName varchar2) is

select index_name

from user_indexes

where table_name = vTname

and partitioned = 'NO'

and (tablespace_name not like vTspName or status like 'UNUSABLE')

order by index_name ;

begin

for ndxRow in ndxCur(a_tname, a_destTS) loop

sqlStmnt := 'alter index '||ndxRow.index_name||

' rebuild tablespace '||a_destTS;

execute immediate sqlStmnt ;

end loop;

end mvNdx;