近日在查詢某項日志的時候，發現查詢非常緩慢，根據以往的經驗這是由于某個日志表過大引起的，為了加快查詢，決定將大部分的歷史數據遷移到另外一個表中，本文主要記錄刪除這個大表的過程，就解決問題而言還有很多方法，但是本文側重點在于如何處理大數據量刪除的操作，及其善后工作。

首先看看我們要做數據遷移的表所占的空間：

SQL> select segment_name , bytes/1048576 MB2 from user_segments3 order by bytes4 /SEGMENT_NAME MB -------------------------------------------------- ---------- TAB_NODE_PARAM .375 SYS_LOB0000053047C00004$$ .4375 PK_TAB_FLOW_HIS 1 PK_TAB_NODE_HIS 2 TAB_TASK_HIS 2 TAB_FLOW_HIS 4 TAB_NODE_HIS 17 PK_TAB_APPEXCHANGE_LOG 152 TAB_APPEXCHANGE_LOG 768

看看歷史數據大概所占的空間比例：

SQL> select count(*) from tab_appexchange_log2 where receive_time >= to_date('2009-01-01','yyyy-mm-dd')3 union all4 select count(*) from tab_appexchange_log5 where receive_time < to_date('2009-01-01','yyyy-mm-dd') SQL> /COUNT(*) ----------15842984037710

從結果來看，的確存在很多歷史數據，我們將 2009 年之前的數據遷移到別的表中。

SQL> create table tab_appexchange_log_200901012 as3 select * from tab_appexchange_log4 where receive_time < to_date('2009-01-01','yyyy-mm-dd') SQL> /Table created.SQL> select count(*) from tab_appexchange_log_200901012 /COUNT(*) ----------4037710

數據已經遷移一份到新的表里面了，可以刪除原表中的舊數據了。

SQL> delete tab_appexchange_log where receive_time < to_date('2009-01-01','yyyy-mm-dd')2 / delete tab_appexchange_log where receive_time < to_date('2009-01-01','yyyy-mm-dd')* ERROR at line 1: ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS2'

這里出現了一個 ORA-30036的錯誤，大概意思是undo表空間不足，看來是要刪除的數據太多了，于是考慮化整為零，刪一點，commit一下。

?

SQL> delete tab_appexchange_log where receive_time < to_date('2007-01-01','yyyy-mm-dd')2 /0 rows deleted.SQL> delete tab_appexchange_log where receive_time < to_date('2008-01-01','yyyy-mm-dd')2 /1586227 rows deleted.SQL> commit;Commit complete.SQL> delete tab_appexchange_log where receive_time < to_date('2008-06-01','yyyy-mm-dd')2 /813019 rows deleted.SQL> commit;Commit complete.SQL> delete tab_appexchange_log where receive_time < to_date('2009-01-01','yyyy-mm-dd')2 /1638464 rows deleted.SQL> commit;Commit complete.

可以預知，現在表里面的數據應該只有 15***** 條，以下證實了這個想法。

SQL> select count(*) from tab_appexchange_log2 /COUNT(*) ----------1584522

我們再看看表占用空間的情況：

SQL> select segment_name , bytes/1048576 MB2 from user_segments3 order by bytes asc;SEGMENT_NAME MB -------------------------------------------------- ---------- TAB_NODE_PARAM .375 SYS_LOB0000053047C00004$$ .4375 PK_TAB_FLOW_HIS 1 PK_TAB_NODE_HIS 2 TAB_TASK_HIS 2 TAB_FLOW_HIS 4 TAB_NODE_HIS 17 PK_TAB_APPEXCHANGE_LOG 152 TAB_APPEXCHANGE_LOG_20090101 552 TAB_APPEXCHANGE_LOG 768

看來原表所占的空間還沒有釋放，這個涉及一個HWM的說法，還有可以觀察到在TAB_APPEXCHANGE_LOG上面的一個索引 PK_TAB_APPEXCHANGE_LOG 空間沒有回收。

我們回過頭來看看剛才的操作對undo表空間產生的壓力：

TO_CHAR(BEGIN_TIME, TO_CHAR(END_TIME,'Y UNDOBLKS ------------------- ------------------- ---------- 2009-06-30 13:39:32 2009-06-30 13:46:35 96164 2009-06-30 13:29:32 2009-06-30 13:39:32 89316 2009-06-30 13:19:32 2009-06-30 13:29:32 209885 2009-06-30 13:09:32 2009-06-30 13:19:32 105 2009-06-30 12:59:32 2009-06-30 13:09:32 125 2009-06-30 12:49:32 2009-06-30 12:59:32 74 2009-06-30 12:39:32 2009-06-30 12:49:32 80 2009-06-30 12:29:32 2009-06-30 12:39:32 72 2009-06-30 12:19:32 2009-06-30 12:29:32 76 2009-06-30 12:09:32 2009-06-30 12:19:32 89 2009-06-30 11:59:32 2009-06-30 12:09:32 126

呵呵，剛才DML的操作對undo表空間產生的壓力還是非常可觀的。

我們開始回收表所占的空間：

SQL> alter table TAB_APPEXCHANGE_LOG enable row movement;Table altered.SQL> alter table TAB_APPEXCHANGE_LOG shrink space;Table altered.

?

由于這么大規模的刪除，會導致很多索引中存在很多標記為刪除的節點，占地方還不能發揮作用，因此重建索引將這些無用節點清理出去才是上策。

SQL> analyze index PK_TAB_APPEXCHANGE_LOG validate structure;Index analyzed.SQL> select height, name , LF_ROWS_LEN,LF_ROWS,DEL_LF_ROWS,DEL_LF_ROWS_LEN from index_stats;HEIGHT NAME LF_ROWS_LEN LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN ---------- ------------------------------ ----------- ---------- ----------- ---------------3 PK_TAB_APPEXCHANGE_LOG 88905533 5621608 4036429 63558680SQL> alter index PK_TAB_APPEXCHANGE_LOG rebuild online;Index altered.SQL> analyze index PK_TAB_APPEXCHANGE_LOG validate structure;Index analyzed.SQL> select height, name , LF_ROWS_LEN,LF_ROWS,DEL_LF_ROWS,DEL_LF_ROWS_LEN from index_stats;HEIGHT NAME LF_ROWS_LEN LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN ---------- ------------------------------ ----------- ---------- ----------- ---------------3 PK_TAB_APPEXCHANGE_LOG 25347109 1585195 0 0

如果現在我們再查一下索引所占的空間的話，會發現所占空間是沒有減少的，和表一樣，同樣也是需要收縮一下空間。

SQL> alter index PK_TAB_APPEXCHANGE_LOG shrink space;Index altered.SQL> select segment_name , bytes/1048576 MB2 from user_segments3 order by bytes4 /SEGMENT_NAME MB -------------------------------------------------- ---------- TAB_NODE_PARAM .375 SYS_LOB0000053047C00004$$ .4375 PK_TAB_FLOW_HIS 1 TAB_TASK_HIS 2 PK_TAB_NODE_HIS 2 TAB_FLOW_HIS 4 TAB_NODE_HIS 17 PK_TAB_APPEXCHANGE_LOG 28.1875 TAB_APPEXCHANGE_LOG 220.8125 TAB_APPEXCHANGE_LOG_20090101 552

?

自此一次刪除大表的操作完成，回顧一下，主要有以下幾點：

1。分批刪除數據，按批次提交。

2?；厥毡硭嫉目臻g。

3。處理由于表收縮所導致的索引失效問題。

《新程序員》：云原生和全面數字化實踐50位技術專家共同創作，文字、視頻、音頻交互閱讀

總結

以上是生活随笔為你收集整理的[原]Oracle删除大表并回收空间的过程的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。