oracle大表drop,Oracle 快速Drop大表

周末一兄弟问我有个近200G的表想要DROP，但是他就算先truncate再drop，或是先offline，再drop，都要话费很长时间，之后又试了分批回缩，发现效果要快很多，只是步骤比较麻烦一点。

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>