达梦数据库分区表新增分区键值interval类型（在线重定义）

然科

已于 2024-01-17 23:03:34 修改

阅读量1.0k

点赞数 19

文章标签：数据库 oracle linux

于 2024-01-16 17:08:08 首次发布

本文链接：https://blog.csdn.net/qr8345776/article/details/135627285

版权

一、任务目的：

1、原分区表没有interval间隔时间参数，在不影响生产业务的情况下，在线修改分区表结构，新增interval类型。

2、达梦在线重定义功能

二、测试环境准备

原数据表定义：

CREATE TABLE "SYSDBA"."PART_TABLE_NOT_INTERVAL"
(
"PLAN_DATE" TIMESTAMP(0) NOT NULL,
"TASK_NAME" VARCHAR2(255),
"TASK_ID" NUMBER(38,0) NOT NULL,
"OBJECT_CLASS" NUMBER(38,0))
PARTITION BY RANGE("PLAN_DATE")
(
PARTITION  "TPD_PART_18"  VALUES LESS THAN(DATETIME'2023-03-02 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_19"  VALUES LESS THAN(DATETIME'2023-03-09 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_20"  VALUES LESS THAN(DATETIME'2023-03-16 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_21"  VALUES LESS THAN(DATETIME'2023-03-23 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_22"  VALUES LESS THAN(DATETIME'2023-03-30 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_23"  VALUES LESS THAN(DATETIME'2023-04-06 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_24"  VALUES LESS THAN(DATETIME'2023-04-13 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_25"  VALUES LESS THAN(DATETIME'2023-04-20 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_26"  VALUES LESS THAN(DATETIME'2023-04-27 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_27"  VALUES LESS THAN(DATETIME'2023-05-04 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_28"  VALUES LESS THAN(DATETIME'2023-05-11 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_29"  VALUES LESS THAN(DATETIME'2023-05-18 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_30"  VALUES LESS THAN(DATETIME'2023-05-25 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_31"  VALUES LESS THAN(DATETIME'2023-06-01 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_32"  VALUES LESS THAN(DATETIME'2023-06-08 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_33"  VALUES LESS THAN(DATETIME'2023-06-15 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_34"  VALUES LESS THAN(DATETIME'2023-06-22 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_35"  VALUES LESS THAN(DATETIME'2023-06-29 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_36"  VALUES LESS THAN(DATETIME'2023-07-06 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_37"  VALUES LESS THAN(DATETIME'2023-07-13 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_38"  VALUES LESS THAN(DATETIME'2023-07-20 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_39"  VALUES LESS THAN(DATETIME'2023-07-27 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_40"  VALUES LESS THAN(DATETIME'2023-08-03 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_41"  VALUES LESS THAN(DATETIME'2023-08-10 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_42"  VALUES LESS THAN(DATETIME'2023-08-17 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_43"  VALUES LESS THAN(DATETIME'2023-08-24 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_44"  VALUES LESS THAN(DATETIME'2023-08-31 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_45"  VALUES LESS THAN(DATETIME'2023-09-07 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_46"  VALUES LESS THAN(DATETIME'2023-09-14 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_47"  VALUES LESS THAN(DATETIME'2023-09-21 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_48"  VALUES LESS THAN(DATETIME'2023-09-28 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_49"  VALUES LESS THAN(DATETIME'2023-10-05 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_50"  VALUES LESS THAN(DATETIME'2023-10-12 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_51"  VALUES LESS THAN(DATETIME'2023-10-19 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_52"  VALUES LESS THAN(DATETIME'2023-10-26 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_53"  VALUES LESS THAN(DATETIME'2023-11-02 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_54"  VALUES LESS THAN(DATETIME'2023-11-09 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_55"  VALUES LESS THAN(DATETIME'2023-11-16 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_56"  VALUES LESS THAN(DATETIME'2023-11-23 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_57"  VALUES LESS THAN(DATETIME'2023-11-30 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_58"  VALUES LESS THAN(DATETIME'2023-12-07 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_59"  VALUES LESS THAN(DATETIME'2023-12-14 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_60"  VALUES LESS THAN(DATETIME'2023-12-21 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_61"  VALUES LESS THAN(DATETIME'2023-12-28 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_62"  VALUES LESS THAN(DATETIME'2024-01-04 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) 
) STORAGE(ON "DMHR", CLUSTERBTR) ;

说明：原分区表按7天间隔创建分区，但是不会自动新增分区。当插入不在分区范围内的数据时，提示报错

在线重定义，新增分区表分区key值interval类型为7天，达到新增数据自动创建分区表的功能。

INTERVAL(NUMTODSINTERVAL(7, 'DAY'))

三、测试过程

1、判断目标表是否可以进行在线重定义。

BEGIN
     dbms_redefinition.can_redef_table(
     'SYSDBA',
     'PART_TABLE_NOT_INTERVAL');
END;
/

报错，无主键表，新增主键

create index PART_TABLE_NOT_INTERVAL_PK on PART_TABLE_NOT_INTERVAL(TASK_ID);
ALTER TABLE PART_TABLE_NOT_INTERVAL MODIFY TASK_ID PRIMARY KEY;

重新执行判断

BEGIN
     dbms_redefinition.can_redef_table(
     'SYSDBA',
     'PART_TABLE_NOT_INTERVAL',
     dbms_redefinition.cons_use_pk);
END;
/

2、创建中间表

CREATE TABLE "SYSDBA"."PART_TABLE_NOT_INTERVAL_TMP"
(
"PLAN_DATE" TIMESTAMP(0) NOT NULL,
"TASK_NAME" VARCHAR2(255),
"TASK_ID" NUMBER(38,0) NOT NULL,
"OBJECT_CLASS" NUMBER(38,0))
PARTITION BY RANGE("PLAN_DATE")
INTERVAL(NUMTODSINTERVAL(7, 'DAY'))--新增interval参数
(
PARTITION  "TPD_PART_18"  VALUES LESS THAN(DATETIME'2023-03-02 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_19"  VALUES LESS THAN(DATETIME'2023-03-09 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_20"  VALUES LESS THAN(DATETIME'2023-03-16 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_21"  VALUES LESS THAN(DATETIME'2023-03-23 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_22"  VALUES LESS THAN(DATETIME'2023-03-30 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_23"  VALUES LESS THAN(DATETIME'2023-04-06 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_24"  VALUES LESS THAN(DATETIME'2023-04-13 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_25"  VALUES LESS THAN(DATETIME'2023-04-20 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_26"  VALUES LESS THAN(DATETIME'2023-04-27 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_27"  VALUES LESS THAN(DATETIME'2023-05-04 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_28"  VALUES LESS THAN(DATETIME'2023-05-11 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_29"  VALUES LESS THAN(DATETIME'2023-05-18 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_30"  VALUES LESS THAN(DATETIME'2023-05-25 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_31"  VALUES LESS THAN(DATETIME'2023-06-01 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_32"  VALUES LESS THAN(DATETIME'2023-06-08 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_33"  VALUES LESS THAN(DATETIME'2023-06-15 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_34"  VALUES LESS THAN(DATETIME'2023-06-22 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_35"  VALUES LESS THAN(DATETIME'2023-06-29 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_36"  VALUES LESS THAN(DATETIME'2023-07-06 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_37"  VALUES LESS THAN(DATETIME'2023-07-13 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_38"  VALUES LESS THAN(DATETIME'2023-07-20 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_39"  VALUES LESS THAN(DATETIME'2023-07-27 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_40"  VALUES LESS THAN(DATETIME'2023-08-03 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_41"  VALUES LESS THAN(DATETIME'2023-08-10 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_42"  VALUES LESS THAN(DATETIME'2023-08-17 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_43"  VALUES LESS THAN(DATETIME'2023-08-24 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_44"  VALUES LESS THAN(DATETIME'2023-08-31 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_45"  VALUES LESS THAN(DATETIME'2023-09-07 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_46"  VALUES LESS THAN(DATETIME'2023-09-14 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_47"  VALUES LESS THAN(DATETIME'2023-09-21 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_48"  VALUES LESS THAN(DATETIME'2023-09-28 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_49"  VALUES LESS THAN(DATETIME'2023-10-05 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_50"  VALUES LESS THAN(DATETIME'2023-10-12 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_51"  VALUES LESS THAN(DATETIME'2023-10-19 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_52"  VALUES LESS THAN(DATETIME'2023-10-26 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_53"  VALUES LESS THAN(DATETIME'2023-11-02 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_54"  VALUES LESS THAN(DATETIME'2023-11-09 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_55"  VALUES LESS THAN(DATETIME'2023-11-16 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_56"  VALUES LESS THAN(DATETIME'2023-11-23 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_57"  VALUES LESS THAN(DATETIME'2023-11-30 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_58"  VALUES LESS THAN(DATETIME'2023-12-07 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_59"  VALUES LESS THAN(DATETIME'2023-12-14 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_60"  VALUES LESS THAN(DATETIME'2023-12-21 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_61"  VALUES LESS THAN(DATETIME'2023-12-28 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) ,
PARTITION  "TPD_PART_62"  VALUES LESS THAN(DATETIME'2024-01-04 00:00:00') STORAGE(ON "MAIN", CLUSTERBTR) 
) STORAGE(ON "DMHR", CLUSTERBTR) ;

create index PART_TABLE_NOT_INTERVAL_TMP_PK on PART_TABLE_NOT_INTERVAL_TMP(TASK_ID);
ALTER TABLE PART_TABLE_NOT_INTERVAL_TMP MODIFY TASK_ID PRIMARY KEY;

3、开始重定义

BEGIN    
  DBMS_REDEFINITION.start_redef_table (      
  'SYSDBA',
  'PART_TABLE_NOT_INTERVAL',
  'PART_TABLE_NOT_INTERVAL_TMP',       
  'PLAN_DATE PLAN_DATE,TASK_NAME TASK_NAME,TASK_ID TASK_ID,OBJECT_CLASS OBJECT_CLASS',       
  DBMS_REDEFINITION.CONS_USE_PK); 
END;

期间测试，是否可以正常插入新数据，模拟生产环境

4、将原表上的依赖（索引、约束、授权、统计信息等）复制到新表

DECLARE
num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
  'SYSDBA', 
  'PART_TABLE_NOT_INTERVAL',
  'PART_TABLE_NOT_INTERVAL_TMP',
  1, --是否复制索引
  TRUE, --是否复制触发器
  TRUE, --是否复制约束
  TRUE, --是否复制授权
  TRUE, --复制过程遇到错误时，是否忽略错误
  num_errors,--出现的错误个数
  FALSE,
  FALSE 
  );
END;

查询DBA_REDEFINITION_ERRORS试图查询错误情况

5、重定义过程中手动数据同步

BEGIN
   dbms_redefinition.sync_interim_table(
   'SYSDBA',
   'PART_TABLE_NOT_INTERVAL',
   'PART_TABLE_NOT_INTERVAL_TMP');
END;

6、完成重定义过程

BEGIN
   dbms_redefinition.finish_redef_table(
   'SYSDBA',
   'PART_TABLE_NOT_INTERVAL',
   'PART_TABLE_NOT_INTERVAL_TMP');
END;

说明：重定义过程中会生成物化视图。完成重定义后物化视图自动删除

四、验证非分区表新数据插入

新分区自动创建，数据插入正常

五、拓展

1、在线重定义的适用环境还是很多种的，例如大表数据碎片清理、索引创建

达梦数据库 - 新一代大型通用关系型数据库 | 达梦在线服务平台

然科

关注

19
点赞
踩
16

收藏

觉得还不错? 一键收藏
0
评论
达梦数据库分区表新增分区键值interval类型（在线重定义）

1、原分区表没有interval间隔时间参数，在不影响生产业务的情况下，在线修改分区表结构，新增interval类型。在线重定义，新增分区表分区key值interval类型为7天，达到新增数据自动创建分区表的功能。说明：原分区表按7天间隔创建分区，但是不会自动新增分区。1、在线重定义的适用环境还是很多种的，例如大表数据碎片清理、索引创建。4、将原表上的依赖（索引、约束、授权、统计信息等）复制到新表。1、判断目标表是否可以进行在线重定义。5、重定义过程中手动数据同步。2、达梦在线重定义功能。
复制链接

扫一扫