您还必须考虑可能使索引无效的索引，以解决有关重置默认表空间的问题，我认为这是您要实现的完整过程：
1）移动分区（根据zbrrigschn盲zlets的回答进行PL / SQL循环）
这些是我在定义a_tname，a_destTS，vTname和vTspName的匿名块包装程序中使用的过程-它们应为您提供大致的概念：
procedure mvTabPart (a_tname in varchar2, a_destTS in varchar2) iscursor 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;beginfor 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) iscursor 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);beginfor 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) iscursor ndxCur(vTname varchar2, vTspName varchar2) isselect 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 ;beginfor 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) iscursor 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 ;beginfor 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;
