在 Oracle 11g 中如何将分区表从一个表空间移动到另一个表空间? [英] How do you move a partitioned table from one tablespace to another in Oracle 11g?

问题描述

我有一个属于表空间report的分区表.我想把它移到表空间record.

I have a partitioned table that belongs to tablespace report. I want to move it to tablespace record instead.

一种可能性是删除表并在新表空间中重新创建它，但这对我来说不是一个选择，因为表中的数据需要在移动后继续存在.

One possibility is to drop the table and recreate it in the new tablespace, but that is not an option for me, since there is data in the table that needs to survive the move.

我首先检查分区是否确实属于表空间报告:

I started by checking that the partitions actually belong to tablespace report with:

SELECT * FROM user_tab_partitions WHERE table_name = 'REQUESTLOG';

然后我就尝试了:

ALTER TABLE requestLog MOVE TABLESPACE record;

但这给了我错误 ORA-145111无法对分区对象执行操作".

But that gives me error ORA-145111 "cannot perform operation on a partitioned object".

然后我发现我可以使用以下方法移动单个分区:

Then I found out that I can move individual partitions using:

ALTER TABLE requestLog MOVE PARTITION "2009-12-29" TABLESPACE report;

但是由于表有 60 个分区(基于日期)，并且因为我可能必须为多个系统执行此操作，所以我想遍历所有分区名称，将每个分区名称移动到新表空间.我试过了，但不能让 SQL 正常工作.

But since there are 60 partitions of the table (based on date), and because I may have to do this for several systems, I would like to loop over all the partition names, moving each to the new tablespace. I tried that, but couldn’t quite get the SQL to work.

即使我将所有现有分区都移动到新表空间，创建新分区时仍然存在问题.新分区仍然在旧表空间report中创建.如何更改以便在新表空间记录中创建新分区?

Even if I move all the existing partitions to the new tablespace, there is still a problem when creating new partitions. The new partitions are still created in the old tablespace report. How do I change so that new partitions are created in the new tablespace record?

推荐答案

您还必须考虑可能会失效的索引 - 除了此之外，您还必须解决有关重置默认表空间的问题，我认为这是完整的过程您将要实施的:

You have to consider indexes that may be invalidated as well - to cover your question about resetting the default tablespaces in addition to this, I think this is the full process that you'll want to implement:

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

这些是我在定义 a_tname、a_destTS、vTname 和 vTspName 的匿名块包装器中使用的过程 - 它们应该为您提供总体思路:

These are procedures I use within an anonymous block wrapper that defines a_tname, a_destTS, vTname, and vTspName - they should give you the general idea:

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;

这篇关于在 Oracle 11g 中如何将分区表从一个表空间移动到另一个表空间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！