oracle大批量資料刪除解決方案

標籤：ar   sp   java   for   資料   log   ad   時間   工作   

這段時間系統由於大量曆史資料造成應用響應緩慢，需要對大量曆史資料做資料移轉，原本計劃將原表備份後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 [email protected]_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=1AND (C700020002 = ‘WF:BMCC_EOMS_ITDealFault‘ OR C700020002=‘WF:BJ_EOMS_COMPLAINT‘)AND c3<1380513600select  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 < 1380513600CREATE TABLE ROWID_T367 AS SELECT ROWID ID,C1 FROM T633 WHERE C3 < 1380513600CREATE 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<1380513600CREATE 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 < 1380513600CREATE TABLE ROWID_H367 AS SELECT ROWID ID,Entryid FROM H367 WHERE T0 < 1380513600CREATE 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;BEGINFOR tr IN (SELECT ID FROM ROWID_T367) LOOPDELETE FROM T367 WHERE ROWID=tr.id;n:=n+1;IF MOD(n,5000)=0 THENCOMMIT;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 THENCOMMIT;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 THENCOMMIT;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 THENCOMMIT;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 THENCOMMIT;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

oracle大批量資料刪除解決方案