Oracle删除大表并回收空间的过程

最新推荐文章于 2024-04-08 19:11:04 发布

xuyuhai

最新推荐文章于 2024-04-08 19:11:04 发布

阅读量645

点赞数

分类专栏： ORACLE 文章标签： oracle sql date delete table user

ORACLE 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

近日在查询某项日志的时候，发现查询非常缓慢，根据以往的经验这是由于某个日志表过大引起的，为了加快查询，决定将大部分的历史数据迁移到另外一个表中，本文主要记录删除这个大表的过程，就解决问题而言还有很多方法，但是本文侧重点在于如何处理大数据量删除的操作，及其善后工作。

首先看看我们要做数据迁移的表所占的空间：

SQL> select segment_name , bytes/1048576 MB
2 from user_segments
3 order by bytes
4 /
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_log
2 where receive_time >= to_date('2009-01-01','yyyy-mm-dd')
3 union all
4 select count(*) from tab_appexchange_log
5 where receive_time < to_date('2009-01-01','yyyy-mm-dd')
SQL> /
COUNT(*)
----------
1584298
4037710从结果来看，的确存在很多历史数据，我们将 2009 年之前的数据迁移到别的表中。

SQL> create table tab_appexchange_log_20090101
2 as
3 select * from tab_appexchange_log
4 where receive_time < to_date('2009-01-01','yyyy-mm-dd')
SQL> /
Table created.
SQL> select count(*) from tab_appexchange_log_20090101
2 /
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_log
2 /
COUNT(*)
----------
1584522我们再看看表占用空间的情况：

SQL> select segment_name , bytes/1048576 MB
2 from user_segments
3 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 63558680
SQL> 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 MB
2 from user_segments
3 order by bytes
4 /
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自此一次删除大表的操作完成，回顾一下，主要有以下几点：