oracle大批量数据删除解决方案

浏览数：24 / 时间：2015年06月12日

这段时间系统由于大量历史数据造成应用响应缓慢，需要对大量历史数据做数据迁移，原本计划将原表备份后Create table as 一个新表，量表换名重建索引等解决，但由于服务层的特殊结构，这些表无法做分区，而且这种方式直接会将服务整挂，所以想采用delete掉数据重新压缩表空间和索引方式解决。
预计删除数据量为12个大表，每个表总量为3000W-3亿数据，需要清除2/3的数据，采取步骤如下，以供日后整理

  1. 备份数据

备份数据量需要表空间空余500G，现workflow空余表空间只有90G,需要扩展至少500G
Step.1备份故障和投诉主工单
表名 备注
T367 故障主工单T表(WF:BMCC_EOMS_ITDealFault)
H367 故障主工单H表(WF:BMCC_EOMS_ITDealFault)
T633 投诉主工单T表(WF:BJ_EOMS_Complaint)
H633 投诉主工单H表(WF:BJ_EOMS_Complaint)
远程dblink备份语句示例
Create table T367 as select * from workflow.T367@eoms_product;
Step.2备份工单辅助表
表名 备注
T117 工单状态表（WF:App_Base_Infor）
T118 工单通知表（WF:App_Base_Notice）
T136 工单环节表（WF:App_DealProcess）
T135 工单流程线表（WF:App_DealLink）
T134 工单开始结束状态表（WF:App_DealAssistantProcess）
T141 工单流程判断表（WF:App_DealVerdict）
T363 工单流转线表（WF:App_DealGoLine）
T114 工单字段修改记录表（WF:App_Base_FieldModifyLog）

  1. 查询数据量

1.故障T表

select count(1) from t367 WHERE 1=1
 AND c3<1380513600

2.投诉T表

select count(1) from t633 WHERE 1=1
 AND c3<1380513600

2.其它辅助表

select count(1) from t136  WHERE 1=1
AND (C700020002 = ‘WF:BMCC_EOMS_ITDealFault‘ OR C700020002=‘WF:BJ_EOMS_COMPLAINT‘)
AND c3<1380513600
select  count(1) from T135 WHERE 1=1 AND (C700020502 = ‘WF:BMCC_EOMS_ITDealFault‘ OR C700020502=‘WF:BJ_EOMS_COMPLAINT‘)  AND c3<1380513600 ;
select  count(1) from T134 WHERE 1=1 AND (C700020802 = ‘WF:BMCC_EOMS_ITDealFault‘ OR C700020802=‘WF:BJ_EOMS_COMPLAINT‘)  AND c3<1380513600 ;
select  count(1) from T141 WHERE 1=1 AND (C700020602 = ‘WF:BMCC_EOMS_ITDealFault‘ OR C700020602=‘WF:BJ_EOMS_COMPLAINT‘)  AND c3<1380513600 ;
select  count(1) from T114 WHERE 1=1 AND (C700021002 = ‘WF:BMCC_EOMS_ITDealFault‘ OR C700021002=‘WF:BJ_EOMS_COMPLAINT‘)  AND c3<1380513600 ;
select count(1) from T118 WHERE 1=1 AND (C700050002 = ‘WF:BMCC_EOMS_ITDealFault‘ OR C700050002=‘WF:BJ_EOMS_COMPLAINT‘)  AND c3>1380513600;
  1. 新建临时表

新建临时表（存要删数据的rowid）
语句：

CREATE TABLE ROWID_T633 AS SELECT ROWID ID,C1 FROM T633 WHERE C3 < 1380513600
CREATE TABLE ROWID_T367 AS SELECT ROWID ID,C1 FROM T633 WHERE C3 < 1380513600
CREATE TABLE ROWID_T117 AS SELECT ROWID AS ID FROM T117 WHERE 1=1
 AND (C700020002 = ‘WF:BMCC_EOMS_ITDealFault‘ OR C700020002=‘WF:BJ_EOMS_COMPLAINT‘)
 AND c3<1380513600
CREATE TABLE ROWID_T135 AS SELECT ROWID AS ID FROM T135 WHERE 1=1 AND (C700020502 = ‘WF:BMCC_EOMS_ITDealFault‘ OR                   C700020502=‘WF:BJ_EOMS_COMPLAINT‘)  AND c3<1380513600 ;
CREATE TABLE ROWID_T134 AS SELECT ROWID AS ID FROM T134 WHERE 1=1 AND (C700020802 = ‘WF:BMCC_EOMS_ITDealFault‘ OR     C700020802=‘WF:BJ_EOMS_COMPLAINT‘)  AND c3<1380513600 ;
CREATE TABLE ROWID_T141 AS SELECT ROWID AS ID FROM T141 WHERE 1=1 AND (C700020602 = ‘WF:BMCC_EOMS_ITDealFault‘ OR     C700020602=‘WF:BJ_EOMS_COMPLAINT‘)  AND c3<1380513600 ;
CREATE TABLE ROWID_T114 AS SELECT ROWID AS ID FROM T114 WHERE 1=1 AND (C700021002 = ‘WF:BMCC_EOMS_ITDealFault‘ OR C700021002=‘WF:BJ_EOMS_COMPLAINT‘)  AND c3<1380513600 ;
CREATE TABLE ROWID_T136 AS SELECT ROWID AS ID FROM T136 WHERE 1=1
 AND (C700020002 = ‘WF:BMCC_EOMS_ITDealFault‘ OR C700020002=‘WF:BJ_EOMS_COMPLAINT‘)
 AND c3<1380513600;
CREATE TABLE ROWID_H633 AS SELECT ROWID ID,Entryid FROM H633 WHERE T0 < 1380513600
CREATE TABLE ROWID_H367 AS SELECT ROWID ID,Entryid FROM H367 WHERE T0 < 1380513600
CREATE TABLE ROWID_T118 AS SELECT ROWID AS ID FROM T118 WHERE 1=1 AND (C700050002 = ‘WF:BMCC_EOMS_ITDealFault‘ OR     C700050002=‘WF:BJ_EOMS_COMPLAINT‘)  AND c3>1380513600;

  1. 写脚本删除

    T117

    DECLARE n NUMBER :=0;
    BEGIN
    FOR tr IN (SELECT ID FROM ROWID_T117) LOOP
    DELETE FROM T117 WHERE ROWID=tr.id;
    n:=n+1;
    IF MOD(n,5000)=0 THEN
    COMMIT;
    END IF;
    END LOOP;
    COMMIT;
    END;

T367

DECLARE n NUMBER :=0;
BEGIN
FOR tr IN (SELECT ID FROM ROWID_T367) LOOP
DELETE FROM T367 WHERE ROWID=tr.id;
n:=n+1;
IF MOD(n,5000)=0 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
END;

T633
DECLARE n NUMBER :=0;
BEGIN
FOR tr IN (SELECT ID FROM ROWID_T633) LOOP
DELETE FROM T633 WHERE ROWID=tr.id;

n:=n+1;
IF MOD(n,5000)=0 THEN
COMMIT;
END IF;

END LOOP;
COMMIT;
END;

T134
DECLARE n NUMBER :=0;
BEGIN
FOR tr IN (SELECT ID FROM ROWID_T134) LOOP
DELETE FROM T134 WHERE ROWID=tr.id;

n:=n+1;
IF MOD(n,5000)=0 THEN
COMMIT;
END IF;

END LOOP;
COMMIT;
END;
…

  1. 剩余表迁移数据工作步骤
    重建T表（T135、T136、T114、T141）的临时表

    CREATE TABLE ROWID_T135 AS SELECT ROWID AS ID FROM T135 WHERE 1=1 AND (C700020502 = ‘WF:BMCC_EOMS_ITDealFault‘ OR C700020502=‘WF:BJ_EOMS_COMPLAINT‘) AND c3<1380513600 ;
    CREATE TABLE ROWID_T141 AS SELECT ROWID AS ID FROM T141 WHERE 1=1 AND (C700020602 = ‘WF:BMCC_EOMS_ITDealFault‘ OR C700020602=‘WF:BJ_EOMS_COMPLAINT‘) AND c3<1380513600 ;
    CREATE TABLE ROWID_T114 AS SELECT ROWID AS ID FROM T114 WHERE 1=1 AND (C700021002 = ‘WF:BMCC_EOMS_ITDealFault‘ OR C700021002=‘WF:BJ_EOMS_COMPLAINT‘) AND c3<1380513600 ;
    CREATE TABLE ROWID_T136 AS SELECT ROWID AS ID FROM T136 WHERE 1=1 AND (C700020002 = ‘WF:BMCC_EOMS_ITDealFault‘ OR C700020002=‘WF:BJ_EOMS_COMPLAINT‘) AND c3<1380513600 ;

    执行脚本删除数据

    DECLARE n NUMBER :=0;
    BEGIN
    FOR tr IN (SELECT ID FROM ROWID_T135) LOOP
    DELETE FROM T135 WHERE ROWID=tr.id;
    n:=n+1;
    IF MOD(n,5000)=0 THEN
    COMMIT;
    END IF;
    END LOOP;
    COMMIT;
    END;
    DECLARE n NUMBER :=0;
    BEGIN
    FOR tr IN (SELECT ID FROM ROWID_T136) LOOP
    DELETE FROM T136 WHERE ROWID=tr.id;
    n:=n+1;
    IF MOD(n,5000)=0 THEN
    COMMIT;
    END IF;
    END LOOP;
    COMMIT;
    END;

DECLARE n NUMBER :=0;
BEGIN
FOR tr IN (SELECT ID FROM ROWID_T114) LOOP
DELETE FROM T114 WHERE ROWID=tr.id;

n:=n+1;
IF MOD(n,5000)=0 THEN
COMMIT;
END IF;

END LOOP;
COMMIT;
END;
DECLARE n NUMBER :=0;
BEGIN
FOR tr IN (SELECT ID FROM ROWID_T141) LOOP
DELETE FROM T141 WHERE ROWID=tr.id;

n:=n+1;
IF MOD(n,5000)=0 THEN
COMMIT;
END IF;

END LOOP;
COMMIT;
END;

  1. 释放表空间

    –打开行锁
    alter table T118 enable row MOVEMENT
    –整理数据和索引
    alter TABLE T118 shrink space COMPACT CASCADE
    –释放表空间
    alter TABLE T118 shrink SPACE CASCADE

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

标签： class java com log 数据 si Go it for class java com log 数据 si Go it for

相关文章

随机文章

您可能还喜欢

您可能还喜欢