200G大表刪除資料方案

lusklusklusk發表於2017-03-08
表總量200G,6億條記錄,每天增量400M,100萬行,需要刪除15天前的資料。

可選方案如下,權衡後選擇方案2
1.不停業務,按時間欄位來進行刪除,每天刪除20G資料(每次刪除大概5G,分四次完成),預計10天完成
優點:該表對應的業務不受影響
缺點:
時間長
時間欄位沒有索引,直接按時間欄位刪除太慢,時間欄位建立索引的話,索引段就20G,索引建立時間超過1小時,期間還可能因為臨時空間不足導致建立失敗
時間欄位建立索引後,刪除20G資料需要4小時,且回滾段大大佔用,容易引發其他程式報錯回滾段不足


2.停業務,透過expdp 按條件匯出最近15天資料,再對原表原索引原約束等物件改名,再impdp
優點:時間短,1小時即可完成,且可以對原表備份便於以後回滾
缺點:該表對應的業務必須停止1小時



操作步驟
步驟1:EXPDP匯出,耗時40分鐘
expdp system/manager directory=DUMP_DIR dumpfile=cuxmsc20170307.dmp tables=cux.cuxmsc query=cux.cuxmsc:\" where CREATION_DATE\>sysdate-15\"

步驟2:修改原索引、原約束、原觸發器等的名稱(system使用者下操作)
alter index CUX.cuxmsc_N1 rename to cuxmsc_N1170307;

alter table cux.cuxmsc rename constraint cuxmsc_PK to cuxmsc_PK170307;
alter table cux.cuxmsc rename constraint SYS_C00938162 to SYS_C00938162170307;

步驟3:修改原表表名,即備份原表(system使用者下操作)
ALTER TABLE cux.cuxmsc RENAME TO cuxmsc20170307

步驟4:IMPDP匯入,耗時3分鐘
impdp system/manager directory=DUMP_DIR dumpfile=cuxmsc20170307.dmp

步驟5:一週之內沒有問題的話刪除備份的原表
drop table cux.cuxmsc20170307 purge



expdp query也會匯出索引、約束、觸發器、comment、許可權等資訊
需要修改原索引、原約束、原觸發器名稱的原因是因為impdp會提示存在這些物件,導致這些物件無法匯入

當然導完後新表的dba_segments.bytes變成了6G,原表是200G,但是新表的dba_tables.num_rows和dba_tables.blocks和原表一樣,不代表高水位沒有降下來,只要重新收集一下統計資訊的話,新表的
dba_tables.num_rows和dba_tables.blocks就變成實際的了

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/30126024/viewspace-2134899/,如需轉載,請註明出處,否則將追究法律責任。

相關文章