drop 大表的一些技巧

drop表之前，先看看是否有约束，如果有，可以将先将约束删除。下面我们来看看如何删除一个178G，有12亿左右数据的表。

由于在执行drop或者truncate的时候，oracle会自动的清理extent，一般对于这样的大表，会extent很多次，笔者的这个表已经extent 近4000次，对于这样的大表，回缩extent的时候往往会影响机器性能，我们采用分批回缩的方法来实现。

主要步骤：

1、清空数据并且保留原来的extent

truncate table table_name reuse storage;

注：reuse storage 是truncate的一个参数，表示保持原来的存储不变，一般我们写的truncate table table_name 就是truncate table table_name drop storage; drop storage是truncate table的默认参数。


2、逐步回缩extent

ALTER table table_name DEALLOCATE UNUSED KEEP 175000M;

ALTER table table_name DEALLOCATE UNUSED KEEP 172000M;

ALTER table table_name DEALLOCATE UNUSED KEEP 169000M;

ALTER table table_name DEALLOCATE UNUSED KEEP 166000M;

……

ALTER table subscription_history_old DEALLOCATE UNUSED KEEP 10M;

注：在执行的时候，可以根据实际情况调整每次回缩空间的大小。


3、完成回缩后drop表

drop table subscription_history_old ;


以下是具体的操作过程：

已连接到 Oracle9i Enterprise Edition Release 9.2.0.6.0 

已连接为 misc



SQL> set timing on

SQL> select * from dual;


DUMMY

-----

X


已执行耗时 0.468 秒


SQL> truncate table subscription_history_old reuse storage;


表被截短


已执行耗时 2.984 秒


SQL> 

SQL> SELECT bytes/1024/1024/1024,a.owner,a.segment_name FROM Dba_Segments a

2 WHERE segment_name LIKE '%SUBSCRIPTION_HISTORY_OLD%';


BYTES/1024/1024/1024 OWNER SEGMENT_NAME

-------------------- ------------------------------ --------------------------------------------------------------------------------

178.705078125 MISC SUBSCRIPTION_HISTORY_OLD


已执行耗时 0.266 秒


SQL> ALTER table subscription_history_old DEALLOCATE UNUSED KEEP 177000M;


表被改变


已执行耗时 1.468 秒


SQL> ALTER table subscription_history_old DEALLOCATE UNUSED KEEP 172000M;


表被改变


已执行耗时 0.782 秒


SQL> ALTER table subscription_history_old DEALLOCATE UNUSED KEEP 162000M;


表被改变


已执行耗时 1.735 秒


SQL> ALTER table subscription_history_old DEALLOCATE UNUSED KEEP 142000M;


表被改变


已执行耗时 3.672 秒


SQL> ALTER table subscription_history_old DEALLOCATE UNUSED KEEP 122000M;


表被改变


已执行耗时 4.984 秒


SQL> ALTER table subscription_history_old DEALLOCATE UNUSED KEEP 102000M;


表被改变


已执行耗时 5.25 秒


SQL> ALTER table subscription_history_old DEALLOCATE UNUSED KEEP 82000M;


表被改变


已执行耗时 5.125 秒


SQL> ALTER table subscription_history_old DEALLOCATE UNUSED KEEP 62000M;


表被改变


已执行耗时 5.063 秒


SQL> ALTER table subscription_history_old DEALLOCATE UNUSED KEEP 42000M;


表被改变


已执行耗时 4.375 秒


SQL> ALTER table subscription_history_old DEALLOCATE UNUSED KEEP 22000M;


表被改变


已执行耗时 4.703 秒


SQL> ALTER table subscription_history_old DEALLOCATE UNUSED KEEP 2000M;


表被改变


已执行耗时 4.187 秒


SQL> ALTER table subscription_history_old DEALLOCATE UNUSED KEEP 500M;


表被改变


已执行耗时 0.375 秒


SQL> 

SQL> SELECT bytes/1024/1024/1024,a.owner,a.segment_name FROM Dba_Segments a

2 WHERE segment_name LIKE '%SUBSCRIPTION_HISTORY_OLD%';


BYTES/1024/1024/1024 OWNER SEGMENT_NAME

-------------------- ------------------------------ --------------------------------------------------------------------------------

0.48834228515625 MISC SUBSCRIPTION_HISTORY_OLD


已执行耗时 1.688 秒


SQL> drop table SUBSCRIPTION_HISTORY_OLD;


表被删掉


已执行耗时 6.453 秒


SQL>