请选择 进入手机版 | 继续访问电脑版

oracle表分区交换实现数据迁移

[复制链接]
滚雪球少年 发表于 2020-12-31 18:56:57 | 显示全部楼层 |阅读模式 打印 上一主题 下一主题
配景

之前公司做的一个消息中心功能优化的需求,我负责了此中一小部分。大抵情况是这样的。
有一张汗青消息表msg_message_list,存放近一年所有用户的消息记载,数据量是千万级的。因此设计的是一张按月的分区表。时间再往前,超过一年的汗青消息则结转到别的一张msg_message_history表。
我负责的是汗青数据的搬运,即把符适时间条件的数据从msg_message_list搬运到msg_message_history。采取的分区互换方式实现
整体实现步调如下

  • 首先对外提供了一个http的接口用于实现该功能,数据层调用数据库的存储过程。
  • 在公司现有的平台上设置了一个定时调用该接口的任务。天天定时触发。
数据库的详细实现

因为一开始,同事就给我指明白方向,使用分区互换技能。所以我也没怎么纠结,就是看存储过程详细如何写了。
表和DDL

需要用到以下三张表,缺一不可
序号表名是否分区表备注1MSG_MESSAGE_LIST是逾期数据的来源––––2MSG_MESSAGE_HISTORY是逾期数据的目标地––––3MSG_MESSAGE_TEMP否分区互换时必须用到的暂时表表格中的三张表结构一样,只是除temp表外其他两张是分区表。ddl如下(部分字段省略):
  1.   CREATE TABLE "USERDB"."MSG_MESSAGE_LIST"   ("MSG_ID" VARCHAR2(36) NOT NULL ENABLE,        "MSG_TITLE" VARCHAR2(128) NOT NULL ENABLE,        "MSG_BODY" VARCHAR2(2000) NOT NULL ENABLE,        "ACTION_URL" VARCHAR2(512),        "MSG_TIME" DATE NOT NULL ENABLE,        "OWNER_ID" VARCHAR2(50)   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  STORAGE(  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)  TABLESPACE "USERS"  PARTITION BY RANGE ("MSG_TIME") INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) (PARTITION "P1"  VALUES LESS THAN (TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)  TABLESPACE "USERS" )
复制代码
通过上面的ddl可以看出,分区表是根据msg_time字段分区的。且在建表时直接创建了一个2017-01-01的分区 。
分区互换存储过程

分区互换的存储过程分为如下步调
–1.查询msg_message_list表的分区数量
–2.当msg_message_list表的分区数量不止一个时,查询最早的分区信息
–3.查询分区最大时间是否在一年前(和入参upperDate有关),满足条件继承举行下面的步调
–4.互换分区数据
– 4.1互换msg_message_list表分区数据至msg_message_temp暂时表
– 4.2汗青表msg_message_history中插入一条数据,产生分区,然后回滚数据
– 4.3互换单表msg_message_temp数据到msg_message_history汗青分区表
– 5 删除MSG_MESSAGE_LIST表分区
  1. CREATE OR REPLACE PROCEDURE "USERDB"."PROC_DROP_PARTITION_MSG_LIST"(upperDate IN date, code OUT NUMBER, remark OUT VARCHAR2) Authid Current_User as-- 将msg_message_list表汗青数据迁移到msg_message_history  dropPartitionName          varchar2(200);     -- msg_message_list表最早分区的PARTITION_NAME  dropPartitionHighValue     varchar2(200);     -- msg_message_list表最早分区的HIGH_VALUE  maxPartitionDate           date;              -- msg_message_list表最早分区内数据的最大时间  v_date                     varchar2(200);     -- msg_message_list表最早分区内数据的最大时间  v_sql_maxdate_partition    varchar2(500);     -- sql,查询分区内数据的最大时间  v_sql_exchange_partition1  varchar2(500);     -- sql,互换分区list->temp  v_sql_exchange_partition2  varchar2(500);     -- sql,互换分区temp->history          v_sql_drop_partition       varchar2(500);     -- sql,删除msg_message_list的分区  v_sql_create_partition     varchar2(500);     -- sql, 将msg_message_temp的一条数据插入汗青表msg_message_history  partionTotalNum            number;            -- msg_message_list表的分区数量  err_msg                    varchar2(2000);  err_code                   number;begin   --1.查询msg_message_list表的分区数量        SELECT count(*) into partionTotalNum   FROM USER_TAB_PARTITIONS T   WHERE T.TABLE_NAME = 'MSG_MESSAGE_LIST';      IF partionTotalNum  upperDate then     dbms_output.put_line(dropPartitionName ||'分区内数据最大日期为' || maxPartitionDate || ',无需删除');     code := 0;     remark := '分区' || dropPartitionName || '内数据最大日期为' || v_date || ',无需删除';     return;  END IF;  --4.互换分区数据  IF maxPartitionDate is not null then    -- 4.1互换msg_message_list表分区数据至msg_message_temp暂时表    v_sql_exchange_partition1 := 'Alter table MSG_MESSAGE_LIST exchange partition ' ||                                dropPartitionName ||                                ' with table MSG_MESSAGE_TEMP WITHOUT VALIDATION';    execute immediate v_sql_exchange_partition1;        dbms_output.put_line('互换LIST表分区至暂时表:' || dropPartitionName||' ('|| dropPartitionHighValue||') ');    commit;        -- 4.2汗青表msg_message_history中插入一条数据,产生分区,然后回滚    v_sql_create_partition := 'insert into MSG_MESSAGE_HISTORY select * from MSG_MESSAGE_TEMP where rownum=1';    execute immediate v_sql_create_partition;    rollback;      dbms_output.put_line('HIS表创建分区完毕');      -- 4.3互换单表msg_message_temp数据到msg_message_history汗青分区表    v_sql_exchange_partition2 := 'ALTER TABLE MSG_MESSAGE_HISTORY EXCHANGE PARTITION                             FOR(to_date(''' || v_date || '' || ''',''YYYY-MM-DD''))' ||              ' WITH TABLE MSG_MESSAGE_TEMP WITHOUT VALIDATION';    execute immediate v_sql_exchange_partition2;    dbms_output.put_line('互换temp数据至HIS表');     commit;     END IF;    dbms_output.put_line('即将删除LIST表分区:' || dropPartitionName||' ('|| dropPartitionHighValue||')');  -- 5 删除MSG_MESSAGE_LIST表分区  v_sql_drop_partition := 'Alter table MSG_MESSAGE_LIST drop partition ' || dropPartitionName;  execute immediate v_sql_drop_partition;  code := 0;  remark := '删除LIST表分区' || dropPartitionName||' ('|| dropPartitionHighValue||')';  commit;EXCEPTION  WHEN OTHERS THEN    ROLLBACK;    ROLLBACK;    err_code := sqlcode;    err_msg  := subStr(sqlerrm, 1, 1500);    dbms_output.put_line(err_code || err_msg);    insert into MSG_PROC_LOG    values (sysdate, 'PROC_DROP_PARTITION_MSG_LIST', err_code, err_msg, '-1');    commit;    code := -1;    remark := err_code|| ':' ||err_msg;    return;END;
复制代码
注意:


  • 存储过程有入参和返回值,入参upperDate为某月的第一天。返回值code和remark,用实体吸收。
  • temp表是必须的,不可以省略,两个分区表直接互换会报错 ORA-14095: ALTER TABLE EXCHANGE 要求非分区, 非聚簇的表。
  • 虽说在history表里创建了一个分区,但是该分区的名字我并不知道,所以temp向history分区互换时的sql要复杂一些
  • 实在我另有一张日志表,用于记载该存储过程工作的详细步调。
  • 参考毗连https://www.jianshu.com/p/d523831554d7
  • 分区互换时需要关注索引问题。因为我没涉及到这部分,这里就不班门弄斧了。

来源:https://blog.csdn.net/xiaomer367/article/details/111990847
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则


专注素材教程免费分享
全国免费热线电话

18768367769

周一至周日9:00-23:00

反馈建议

27428564@qq.com 在线QQ咨询

扫描二维码关注我们

Powered by Discuz! X3.4© 2001-2013 Comsenz Inc.( 蜀ICP备2021001884号-1 )