Oracle为分区表添加新分区

秒速五厘米 2022-04-13 14:25 516阅读 0赞

### 需求：Message表，在原有分区的基础上添加一个新分区 ###

具体描述：

1.  该表有50张，从Message\_0到Message\_49,数据量比较大，最少的一张有几千万数据，50G左右，最大的一张有几亿数据，超过300G.
2.  该表为list分区表，有default分区，且default分区数据量比较大，占全表数据量的95%以上
3.  该表在三个月前做了联机重定义，将原来未分区的表做了分区，分区后存量数据放在default分区，新写入的数据都在其他分区中
4.  default分区数据现在在业务中基本上不用(存量数据)

爬坑开始：

### 爬坑1.直接add partition 报错： ORA-14323 ###

alter table MESSAGE_0 add partition P_10 values(10);

直接向表中add一个P\_10分区，执行报错，错误代码ORA-14323：list分区表中有default分区存在情况下不能add分区

### 爬坑2.split分区，执行缓慢 ###

alter table MESSAGE_0 split partition P_D values (10) into (partition P_10,partition P_D);

将默认分区p\_d拆成p\_10和p\_d两个分区，执行成功，但是比较慢（17301165条数据，34.79G，耗时162.07秒）

### 爬坑3.exchange分区报错：ORA-14097 ###

交换分区，创建一张结构相同的表MESSAGE\_0\_DEFAULT，将default分区交换到新表。

create table MESSAGE_0_DEFAULT as (select * from  OFFLMG_0 where 1=2);
    
    alter table MESSAGE_0 exchange partition p_d with table MESSAGE_0_DEFAULT;

执行报错，错误代码ORA-14097: ALTER TABLE EXCHANGE PARTITION 中的列类型或大小不匹配

这我就不能忍了，CTAS的表竟然说表结构不相同，经过分析，这张表维护时间超过了三年，可能之前经过了字段的添加和删除，有一些unused的字段存在

执行查询：

select table_name,column_name,data_type,data_length,column_id from user_tab_cols where table_name = 'MESSAGE_0';

果然，有一行记录明显不对

MESSAGE_0 SYS_C00011_15072514:46:10$ VARCHAR2 255

这个字段SYS\_C00011\_15072514:46:10$说明在2015年7月25号的时候确实删除了一个字段，只能看到这个字段是VARCHAR2类型的，大小255，不能看到字段名.

怎么办，怎么办，几个人合计了下，要不把新表也加个相同类型的字段，然后给set unused得了，试一试

--建新表前判断有则删除
    
    drop table MESSAGE_0_DEFAULT cascade constraints;
    
    --按照原有表结构创建新表
    
    create table MESSAGE_0_DEFAULT as (select * from  MESSAGE_0 where 1=2);
    
    --添加一个字段，名字随意，用来set unused的
    
    alter table MESSAGE_0_DEFAULT add (TEST varchar2(255));
    
    --将这个字段set unused
    
    alter table OFFLMG_0_DEFAULT set unused(TEST);
    
    --交换原表的default分区到新表
    
    alter table MESSAGE_0 exchange partition P_D with table OFFLMG_0_DEFAULT;
    
    --删除原表default分区
    
    alter table MESSAGE_0 drop partition P_D;
    
    --为原表添加新分区
    
    alter table MESSAGE_0 add partition P_10 values(10);
    
    --为原表添加default分区
    
    alter table MESSAGE_0 add partition P_D values(default);

执行成功（17301165条数据，34.79G，耗时3毫秒）