drop大表的一些技巧

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

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

主要步骤：

1. 1、清空数据并且保留原来的extent
2. truncate table table_name reuse storage;
3. 注：reuse storage 是truncate的一个参数，表示保持原来的存储不变，一般我们写的truncate table table_name 就是truncate table table_name drop storage; drop storage是truncate table的默认参数。
4. 2、逐步回缩extent
5. ALTER table table_name DEALLOCATE UNUSED KEEP 175000M;
6. ALTER table table_name DEALLOCATE UNUSED KEEP 172000M;
7. ALTER table table_name DEALLOCATE UNUSED KEEP 169000M;
8. ALTER table table_name DEALLOCATE UNUSED KEEP 166000M;
9. ……
10. ALTER table subscription_history_old DEALLOCATE UNUSED KEEP 10M;
11. 注：在执行的时候，可以根据实际情况调整每次回缩空间的大小。
12. 3、完成回缩后drop表
13. drop table subscription_history_old ;
14. 以下是具体的操作过程：
15. 已连接到 Oracle9i Enterprise Edition Release 9.2.0.6.0
16. 已连接为 misc
17. SQL> set timing on
18. SQL> select * from dual;
19. DUMMY
20. -----
21. X
22. 已执行耗时 0.468 秒
23. SQL> truncate table subscription_history_old reuse storage;
24. 表被截短
25. 已执行耗时 2.984 秒
26. SQL>
27. SQL> SELECT bytes/1024/1024/1024,a.owner,a.segment_name FROM Dba_Segments a
28. 2 WHERE segment_name LIKE '%SUBSCRIPTION_HISTORY_OLD%';
29. BYTES/1024/1024/1024 OWNER SEGMENT_NAME
30. -------------------- ------------------------------ --------------------------------------------------------------------------------
31. 178.705078125 MISC SUBSCRIPTION_HISTORY_OLD
32. 已执行耗时 0.266 秒
33. SQL> ALTER table subscription_history_old DEALLOCATE UNUSED KEEP 177000M;
34. 表被改变
35. 已执行耗时 1.468 秒
36. SQL> ALTER table subscription_history_old DEALLOCATE UNUSED KEEP 172000M;
37. 表被改变
38. 已执行耗时 0.782 秒
39. SQL> ALTER table subscription_history_old DEALLOCATE UNUSED KEEP 162000M;
40. 表被改变
41. 已执行耗时 1.735 秒
42. SQL> ALTER table subscription_history_old DEALLOCATE UNUSED KEEP 142000M;
43. 表被改变
44. 已执行耗时 3.672 秒
45. SQL> ALTER table subscription_history_old DEALLOCATE UNUSED KEEP 122000M;
46. 表被改变
47. 已执行耗时 4.984 秒
48. SQL> ALTER table subscription_history_old DEALLOCATE UNUSED KEEP 102000M;
49. 表被改变
50. 已执行耗时 5.25 秒
51. SQL> ALTER table subscription_history_old DEALLOCATE UNUSED KEEP 82000M;
52. 表被改变
53. 已执行耗时 5.125 秒
54. SQL> ALTER table subscription_history_old DEALLOCATE UNUSED KEEP 62000M;
55. 表被改变
56. 已执行耗时 5.063 秒
57. SQL> ALTER table subscription_history_old DEALLOCATE UNUSED KEEP 42000M;
58. 表被改变
59. 已执行耗时 4.375 秒
60. SQL> ALTER table subscription_history_old DEALLOCATE UNUSED KEEP 22000M;
61. 表被改变
62. 已执行耗时 4.703 秒
63. SQL> ALTER table subscription_history_old DEALLOCATE UNUSED KEEP 2000M;
64. 表被改变
65. 已执行耗时 4.187 秒
66. SQL> ALTER table subscription_history_old DEALLOCATE UNUSED KEEP 500M;
67. 表被改变
68. 已执行耗时 0.375 秒
69. SQL>
70. SQL> SELECT bytes/1024/1024/1024,a.owner,a.segment_name FROM Dba_Segments a
71. 2 WHERE segment_name LIKE '%SUBSCRIPTION_HISTORY_OLD%';
72. BYTES/1024/1024/1024 OWNER SEGMENT_NAME
73. -------------------- ------------------------------ --------------------------------------------------------------------------------
74. 0.48834228515625 MISC SUBSCRIPTION_HISTORY_OLD
75. 已执行耗时 1.688 秒
76. SQL> drop table SUBSCRIPTION_HISTORY_OLD;
77. 表被删掉
78. 已执行耗时 6.453 秒
79. SQL>

http://www.oracleblog.org/working-case/skill-of-drop-big-table/