这段时间系统由于大量历史数据造成应用响应缓慢,需要对大量历史数据做数据迁移,原本计划将原表备份后Create table as 一个新表,量表换名重建索引等解决,但由于服务层的特殊结构,这些表无法做分区,而且这种方式直接会将服务整挂,所以想采用delete掉数据重新压缩表空间和索引方式解决。 预计删除数据量为12个大表,每个表总量为3000W-3亿数据,需要清除2/3的数据,采取步骤如下,以供日后整理
- 备份数据
备份数据量需要表空间空余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.故障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; 3. 新建临时表
新建临时表(存要删数据的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_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_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_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; 4. 写脚本删除
-- 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; … 5. 剩余表迁移数据工作步骤
重建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;
- 释放表空间
--打开行锁 alter table T118 enable row MOVEMENT --整理数据和索引 alter TABLE T118 shrink space COMPACT CASCADE --释放表空间 alter TABLE T118 shrink SPACE CASCADE