现在的位置: 首页 > 综合 > 正文

分区表的分区与普通表交换的一些问题

2014年09月05日 ⁄ 综合 ⁄ 共 3360字 ⁄ 字号 评论关闭

前几天想要用分区交换的方法将分区表的数据交换出去,结果有的分区交换成功了,有的确失败了。按照网上说的方法也没有很好的解决。

这个技术本来应该是一个很方便的技术的,可是由于不太会用,觉得不是很方便。

转载一篇不错的博客,以备查用。

http://www.cnblogs.com/lanzi/archive/2013/01/24/2875838.html


该文主要介绍分区表如何与一般表进行交换数据,在交换中遇到的几个问题及解决。

实验需要的表及数据
create table t_addr
(
  id                  NUMBER(18) PRIMARY KEY,
  name                VARCHAR2(300),
  tag                 NUMBER
)
partition by list (tag)
(
  partition P_AREA1 values (1),
  partition P_AREA2 values (2),
  partition P_AREA3 values (3),
  partition P_AREA_DEFAULT values (DEFAULT));
 
INSERT INTO t_addr SELECT o.OBJECT_ID,o.OBJECT_NAME,1 FROM dba_objects o;
alter table t_addr enable row movement;
UPDATE t_addr SET tag=1;
UPDATE t_addr SET tag=2 WHERE ROWNUM<=50000;
UPDATE t_addr SET tag=3 WHERE ROWNUM<=40000;
UPDATE t_addr SET tag=5 WHERE ROWNUM<=10000;
BEGIN dbms_stats.gather_table_stats('kgis','t_addr');END;

CREATE TABLE tmp_addr AS SELECT * FROM t_addr WHERE 1=0;

交换的语句:
alter table t_addr
 exchange partition P_AREA2
 with table tmp_addr
 including indexes
 without VALIDATION
 UPDATE GLOBAL INDEXES;
UPDATE GLOBAL INDEXES --这个语句是针对全局索引,如果分区表有全局索引加上该语句就不会失效,否则交换后全局索引会无效需重建

交换表:tmp_addr
分区表:t_addr

ORA-14097: ALTER TABLE EXCHANGE PARTITION 中的列类型或大小不匹配

原因:交换表与要交换分区的表结构要一致,如分区表(t_addr)id是主键,那么交换表(tmp_addr)的id也必须有主键。
ALTER  TABLE tmp_addr ADD PRIMARY KEY(ID)增加后在执行交换就成功了。
分区表创建的主键默认为全局索引,那么交换表对应的字段也需要创建主键;而且不能包含索引交换
(including INDEXES without VALIDATION)。

总结:如果分区表含有主键,那么交换表相应字段也必须有主键;
      而且交换时不能包含索引,否则也会报ORA-14098错误,即只能交换数据。

ORA-14098: ALTER TABLE EXCHANGE PARTITION 中的表索引不匹配
原因:
1.如果分区表的非分区健创建了本地索引,那么交换表的该字段就也必须创建索引,否则报如上该错误,如下例子所示:
create index idx_addrid on t_addr(ID) LOCAL;
create index idx_tmpid on tmp_addr(ID);
alter table t_addr
 exchange partition P_AREA2
 with table tmp_addr
 including indexes
 without VALIDATION
交换成功
DROP INDEX idx_tmpid;
alter table t_addr
 exchange partition P_AREA2
 with table tmp_addr
 including indexes
 without VALIDATION
删除交换表的索引交换报如上错误。

2.如果分区表的非分区健创建了全局索引,那么交换表的该字段就不能创建索引,否则报如上该错误,如下例子所示:
 DROP INDEX   idx_addrid;  
create index idx_addrid on t_addr(ID);
create index idx_tmpid on tmp_addr(ID);  
alter table t_addr
 exchange partition P_AREA2
 with table tmp_addr
 including indexes
 without VALIDATION      
进行交换报如上错误。

DROP INDEX idx_tmpid
alter table t_addr
 exchange partition P_AREA2
 with table tmp_addr
 including indexes
 without VALIDATION
删除索引后交换成功。

注:1和2的测试时,分区健tag在分区表创建的是本地索引,交换表也创建了索引,即如下创建语句:
create index idx_addrtag_local on t_addr(tag) LOCAL
create index idx_tmptag_ on tmp_addr(tag)
      
3.如果分区表的分区健创建了本地索引,那么交换表的该字段也必须创建索引
将id字段的索引删除:
DROP INDEX idx_addrid;--删除分区表的id字段的全局索引,注意交换表的该字段索引也已经删除
现在索引情况是:分区表的分区健为本地索引,交换表对应的该字段也创建了索引,执行交换没有问题。
如果将交换表的该字段索引删除,则报如上错误:
DROP INDEX idx_tmptag_;
alter table t_addr
 exchange partition P_AREA2
 with table tmp_addr
 including indexes
 without VALIDATION

4.如果分区表的分区健创建了全局索引, 交换表的该字段就不能创建索引,否则报如上错误
DROP INDEX idx_addrtag_local;
create index idx_addrtag_local on t_addr(tag);--分区健创建全局索引
--交换表该字段无索引交换成功
 alter table t_addr
 exchange partition P_AREA2
 with table tmp_addr
 including indexes
 without VALIDATION

create index idx_tmptag_ on tmp_addr(tag);
--交换表该字段也创建索引执行交换报如上错误
 alter table t_addr
 exchange partition P_AREA2
 with table tmp_addr
 including indexes
 without VALIDATION
总结:如果交换时包含索引,不管分区健还是非分区健,如果分区表创建的是分区索引,则交换表相应字段也必须创建索引;
如果分区表创建了全局索引,则交换表相应字段就不能创建索引,否则报ORA-14098错误。  

ORA-02266: 表中的唯一/主键被启用的外部关键字引用
原因:分区表中的主键被别的表引用,因此执行交换前必须先把该主键disable及引用该主键的外键disable,同时
      交换表中的主键也必须disable掉与分区表保持一致。
   --对分区表即交换表的主键及引用该主键的外键disable
   alter table partiton_tablename disable primary key cascade;--加上cascade后引用该主键的外键也会disable
   alter table change_tablename disable primary key cascade;
注:由于将主键disable了,因此交换后记得将主键及引用该主键的外键enable。
 

抱歉!评论已关闭.