首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >如何在Oracle 11g中将分区表从一个表空间移动到另一个表空间?

问如何在Oracle 11g中将分区表从一个表空间移动到另一个表空间?
EN

Stack Overflow用户
提问于 2010-01-04 10:16:41
回答 6查看 95K关注 0票数 10

我有一个分区表,它属于表空间报告。我想把它移到表空间记录中。

一种可能是删除该表并在新的表空间中重新创建它,但这对我来说不是一个选项,因为表中有数据需要在移动中生存下来。

我首先检查分区是否实际上属于表空间报告,并使用:

代码语言:javascript
复制
SELECT * FROM user_tab_partitions WHERE table_name = 'REQUESTLOG';

然后我就试着:

代码语言:javascript
复制
ALTER TABLE requestLog MOVE TABLESPACE record;

但这给我带来了错误,ORA-145111“不能对分区对象执行操作”。

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

代码语言:javascript
复制
ALTER TABLE requestLog MOVE PARTITION "2009-12-29" TABLESPACE report;

但是,由于该表有60个分区(基于日期),而且由于可能需要对多个系统执行此操作,所以我希望遍历所有分区名称,将每个分区移动到新的表空间。我试过了,但无法完全使SQL工作。

即使我将所有现有分区移动到新表空间,在创建新分区时仍然存在问题。新分区仍在旧表空间报告中创建。如何更改,以便在新表空间记录中创建新分区

EN

回答 6

Stack Overflow用户

回答已采纳

发布于 2010-01-04 13:52:16

您还必须考虑可能无效的索引--为了解决重置默认表空间的问题,此外,我认为这是您想要实现的整个过程:

1)移动分区( PL/SQL循环,如per zürigschn的答案)

这些是我在定义a_tname、a_destTS、vTname和vTspName的匿名块包装器中使用的过程--它们应该会给您一个大致的想法:

代码语言:javascript
复制
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)设置表默认分区表空间,以便在那里创建新分区:

代码语言:javascript
复制
    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)设置索引默认分区表空间,以便在需要的地方创建新的索引分区(如果有的话):

代码语言:javascript
复制
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)重新构建任何需要重建且不在所需表空间中的分区索引:

代码语言:javascript
复制
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)重建任何全局索引()

代码语言:javascript
复制
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;
票数 26
EN

Stack Overflow用户

发布于 2010-01-04 10:53:01

您可以使用PL/SQL来完成它,也可以用sql生成语句。我决定用简单的SQL生成alter语句:

代码语言:javascript
复制
--set linesize
set lines 100

--This Query generates the alter table statements:
SELECT 'ALTER TABLE '
       ||table_name
       ||' MOVE PARTITION '
       ||partition_name
       ||' TABLESPACE REPORT;'
FROM   all_tab_partitions
WHERE  table_name = 'requestLog'; 

您可以执行上一条语句的输出。

每个用户都有一个默认的表空间。如果在创建/更改时没有指定任何其他对象,则在该默认表空间中创建新的数据库对象。

票数 9
EN

Stack Overflow用户

发布于 2011-10-10 14:39:37

在表空间中移动数据的最简单方法:

移动所有非分区表

代码语言:javascript
复制
SELECT 'ALTER TABLE '||OWNER|| '.'||TABLE_NAME||' MOVE TABLESPACE ARCHIVE;'  
FROM ALL_tables 
where owner = 'owner_name' 
and temporary != 'Y'
and partitioned != 'YES';

分区表

代码语言:javascript
复制
SELECT 'ALTER TABLE '|| TABLE_OWNER||'.'||TABLE_NAME||' MOVE PARTITION ' ||  PARTITION_NAME||  ' TABLESPACE ARCHIVE;'  FROM ALL_tab_partitions 
WHERE TABLE_OWNER = 'owner_name' 
AND table_NAME NOT LIKE 'BIN$%';

非分区索引

代码语言:javascript
复制
SELECT 'ALTER INDEX '|| OWNER||'.'||OBJECT_NAME ||' REBUILD TABLESPACE ARCHIVE ;' 
FROM ALL_OBJECTS 
WHERE OBJECT_TYPE ='INDEX'
AND OWNER = 'owner_name';

分区索引

代码语言:javascript
复制
SELECT  'ALTER INDEX '||I.INDEX_NAME||'REBUILD PARITION'|| S.PARTITION_NAME || ' TABLESPACE  ARCHIVE ' 
                   FROM  DBA_INDEXES I,    DBA_SEGMENTS S
                  WHERE  I.INDEX_NAME = S.SEGMENT_NAME
                    AND I.INDEX_TYPE IN ('NORMAL', 'BITMAP')
                    AND I.OWNER = 'owner_name'; 
票数 4
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/1998627

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档