oracle数据量650G的表（有分区）数据删除方案（分区交换技术）

背景：650G的流程已办记录表太大，用delete删除的话肯定是删不动的，然后因为这个大表肯定是建了分区的，所以打算使用分区交换技术。

具体SQL如下：

------------------执行脚本之前需要用数据泵把BPMS_RU_DONE_TASK导出来------------------
------------------执行脚本过程中用户无法使用上报流程，查询流程跟踪------------------
------------------重要：SQL要一条条执行，千万不要copy到命令窗口一下执行------------------

--先要把表rename的目的相对于把表离线，如果是在线的表很多操作是无法做的
drop table BPMS_RU_DONE_TASK_TEMP;
--如果rename执行失败，则不要往下执行
rename  BPMS_RU_DONE_TASK to BPMS_RU_DONE_TASK_TEMP;

--删除索引的原因是索引也是占空间的，如果表的数据清理了，索引会产生很多碎片
drop index IND_BRDT_ACTIVITY_INS_ID3;
drop index IND_BRDT_CUR_NODE_INS_ID3;
drop index IND_BRDT_MAIN_PROCESS_INS_ID3;
drop index IND_BRDT_TRANS_ACTOR_ID3;
alter table BPMS_RU_DONE_TASK_TEMP drop constraint PK_BPMS_RU_DONE_TASK3;
drop index IND_PK_BPMS_RU_DONE_TASK3;

--准备使用分区交换技术
create table BK0403BPMS_RU_DONE_TASK
(
  done_task_id        VARCHAR2(40) not null,main_process_id     VARCHAR2(40),cur_process_id      VARCHAR2(40),cur_node_id         VARCHAR2(40),cur_node_name       VARCHAR2(60),main_process_ins_id VARCHAR2(40),cur_process_ins_id  VARCHAR2(40),cur_node_ins_id     VARCHAR2(40),activity_ins_id     VARCHAR2(40),status              NUMBER(2),trans_actor_id      VARCHAR2(40),actor_name          VARCHAR2(40),read_flag           NUMBER(1),back_flag           NUMBER(2),expiration          TIMESTAMP(6),urge_time           TIMESTAMP(6),urge_interval       NUMBER(20),urge_flag           NUMBER(1),note                VARCHAR2(4000),note_type           NUMBER(2),create_time         TIMESTAMP(6),version             NUMBER(20),task_complete_type  NUMBER(2) default -1,revoke_back_flag    NUMBER(1) default 1,modify_date         TIMESTAMP(6)
);

--要使用并行,BK0403BPMS_RU_DONE_TASK这个表里面最初的记录是需要保留的数据
--下面的业务逻辑需要相应的开发人员确认
alter table BK0403BPMS_RU_DONE_TASK nologging;

alter session enable parallel dml;
insert /*+ append parallel(16)*/ into BK0403BPMS_RU_DONE_TASK
  (done_task_id,main_process_id,cur_process_id,cur_node_id,cur_node_name,main_process_ins_id,cur_process_ins_id,cur_node_ins_id,activity_ins_id,status,trans_actor_id,actor_name,read_flag,back_flag,expiration,urge_time,urge_interval,urge_flag,note,note_type,create_time,version,task_complete_type,revoke_back_flag)
  select /*+ parallel(T,16) */
         done_task_id,revoke_back_flag
    FROM BPMS_RU_DONE_TASK_TEMP T
   WHERE T.MAIN_PROCESS_ID = 'oneAssetProcess'
     AND T.STATUS = 1;
commit;

alter session disable parallel dml;
alter table BK0403BPMS_RU_DONE_TASK logging;
     
--进行分区交换
alter table BPMS_RU_DONE_TASK_TEMP exchange partition P_ONEASSETPROCESS with table BK0403BPMS_RU_DONE_TASK;

--使用并行建索引
create unique index INDU_BPMS_RU_DONE_TASK4 on BPMS_RU_DONE_TASK_TEMP (DONE_TASK_ID)  Nologging parallel 16;
alter table BPMS_RU_DONE_TASK_TEMP add constraint pk_BPMS_RU_DONE_TASK4 primary key (DONE_TASK_ID);

create index IND_BRDT_ACTIVITY_INS_ID4 on BPMS_RU_DONE_TASK_TEMP (ACTIVITY_INS_ID) Nologging parallel 16;
create index IND_BRDT_CUR_NODE_INS_ID4 on BPMS_RU_DONE_TASK_TEMP (CUR_NODE_INS_ID) Nologging parallel 16;
create index IND_BRDT_MAIN_PROCESS_INS_ID4 on BPMS_RU_DONE_TASK_TEMP (MAIN_PROCESS_INS_ID) Nologging parallel 16;
create index IND_BRDT_TRANS_ACTOR_ID4 on BPMS_RU_DONE_TASK_TEMP (TRANS_ACTOR_ID) Nologging parallel 16;

--一定要关闭索引的并行，要不然，数据库明天一定会hang住
alter index INDU_BPMS_RU_DONE_TASK4 noparallel;
alter index IND_BRDT_ACTIVITY_INS_ID4 noparallel;
alter index IND_BRDT_CUR_NODE_INS_ID4 noparallel;
alter index IND_BRDT_MAIN_PROCESS_INS_ID4 noparallel;
alter index IND_BRDT_TRANS_ACTOR_ID4 noparallel;

alter table BPMS_RU_DONE_TASK_TEMP enable row movement;

--收集统计信息的命令要在命令窗口执行
exec dbms_stats.gather_table_stats(user,'BPMS_RU_DONE_TASK_TEMP',cascade => true,degree => 32,no_invalidate=>FALSE,estimate_percent=> 80);

rename  BPMS_RU_DONE_TASK_TEMP to BPMS_RU_DONE_TASK;

--完成脚本之后需要做验证
1.下发和回退一个流程是否能成功。

2.检查表和索引的并行度，如果没有数据则是正常的，如果有数据则导回来
select s.table_name,s.degree from user_tables s where s.degree >1;

3.把各分区的数据导回来
select s.segment_name,s.partition_name,s.bytes/1024/1024/1024 from user_segments s
 where s.segment_name='BPMS_RU_DONE_TASK';

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!