Oracle_为分区表添加新分区

浏览: 124 发布日期: 2017-12-21 分类: oracle

关键字

  • exchange partition
  • unused
  • ORA-14097
  • ORA-14323
  • CTAS

需求:添加分区

离线消息表OFFLMG,在原有分区的基础上添加一个分区。

分析:数据量大

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

操作

  • 尝试一:add

直接add分区

alter table OFFLMG_0 
add partition P_10 
values(10);

执行报错,错误代码ORA-14323:list分区表中有default分区存在情况下不能add分区

  • 尝试二:split

       spilt分区(P_D分区为default分区)

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

执行成功,但是比较慢(17301165条数据,34.79G,耗时162.07秒),如果在生产上这样搞的话,估计投产当天跑一天都跑不完,这是无法接受的情况,只能另寻他路了

  • 尝试三:exchange

       交换分区,创建一张结构相同的表OFFLMG_0_DEFAULT,将default分区交换到新表。

    create table OFFLMG_0_DEFAULT
    tablespace IMLOG
    as (
    select * from OFFLMG_0 where 1=2
    );
    alter table OFFLMG_0
    exchange partition P_D
    with table OFFLMG_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 = 'OFFLMG_0';

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

OFFLMG_0    SYS_C00011_15072514:46:10$    VARCHAR2    255    

这个字段SYS_C00011_15072514:46:10$说明在2015年7月25号的时候确实删除了一个字段,只能看到这个字段是VARCHAR2类型的,大小255,不能看到字段名.
怎么办,怎么办,几个人合计了下,要不把新表也加个相同类型的字段,然后给set unused得了,试一试

--建新表前判断有则删除
DROP TABLE OFFLMG_0_DEFAULT CASCADE CONSTRAINTS;
--按照原有表结构创建新表
create table OFFLMG_0_DEFAULT 
tablespace IMLOG 
as (
select * from  OFFLMG_0 where 1=2);
--添加一个字段,名字随意,用来set unused的
ALTER TABLE OFFLMG_0_DEFAULT 
ADD (TEST VARCHAR2(255));
--将这个字段set unused
alter table OFFLMG_0_DEFAULT set unused(TEST);    
--交换原表的default分区到新表
alter table OFFLMG_0 
exchange partition P_D 
with table OFFLMG_0_DEFAULT;
--删除原表default分区
alter table OFFLMG_0 
drop partition P_D;        
--为原表添加新分区
alter table OFFLMG_0 
add partition P_10 
values(10);
--为原表添加default分区
alter table OFFLMG_0 
add partition P_D
values(default);

执行成功(17301165条数据,34.79G,耗时3毫秒)

返回顶部