Ò»¡¢Á÷³Ì¼ò½é
1¡¢ÏȲ»Ö±½ÓʹÓÃDrop tableÃüÁȡ¶ø´øÖ®ÓôøÓÐREUSE STORAGE×Ó¾äµÄTuncate tableÃüÁî¡£ÒòΪûÓÐExtentsÒª±»»ØÊÕ£¬Trunate tableÃüÁîÖ´Ðкܿ죬ֻÊǶεÄhighwater mark¼òµ¥µÄ±»µ÷Õûµ½¶ÎÍ·µÄblockÉÏ¡£
2¡¢Ê¹ÓôøÓÐKEEP×Ó¾äµÄalter tableÃüÁîÖ𽥵ĻØÊÕ±íµÄExtents£¬ÒÔ¼õÇá¶ÔCpuµÄѹÁ¦¡£
¶þ¡¢ÒÔÒ»¸öÀý×ÓÀ´ËµÃ÷£º
¼Ù¶¨ÓÐÒ»¸ö±íÃû×ÖΪBIGTAB,ϵͳµÄcpu×ÊÔ´ºÜÓÐÏÞ£¬¸ù¾Ý¹ýÈ¥µÄ¾ÑédropÕâÑùµÄÒ»ÕűíÒª»¨µôÊýÌìµÄʱ¼ä¡£ÈçϵIJÙ×÷¾Í¿ÉÒÔÔÚ²»´óÁ¿ºÄÓÃcpuµÄÇéÐÎϺÜÓÐЧµÄDropÕâÕÅ´ó±í¡£
1. Truncate table, ʹÓÃREUSE STORAGE×Ó¾ä:
SQL> TRUNCATE TABLE BIGTAB REUSE STORAGE;
2. Èç¹ûÒª»¨·Ñ3Ìì(72Сʱ) È¥dropÕâÕűí,¿ÉÒÔ°Ñʱ¼ä·ÖÉ¢µ½6¸öÍíÉÏ£¬Ã¿¸öÍíÉÏdrop 1/3GbÊý¾Ý.
Night 1:
SQL> ALTER TABLE BIGTAB DEALLOCATE UNUSED KEEP 1707M; (2Gb*5/6)
Night 2:
SQL> ALTER TABLE BIGTAB DEALLOCATE UNUSED KEEP 1365M; (2Gb*4/6)
Night 3:
SQL> ALTER TABLE BIGTAB DEALLOCATE UNUSED KEEP 1024M; (2Gb*3/6)
Night 4:
SQL> ALTER TABLE BIGTAB DEALLOCATE UNUSED KEEP 683M; (2Gb*2/6)
Night 5:
SQL> ALTER TABLE BIGTAB DEALLOCATE UNUSED KEEP 341M; (2Gb*1/6)
Night 6:
SQL> DROP TABLE BIGTAB;
ͬÑùµÄ·½·¨Ò²¶ÔLOB¶ÎÊÊÓÃ.
SQL> ALTER TABLE MODIFY LOB ()
DEALLOCATE UNUSED KEEP M;
Èý¡¢ÆäËüÐèҪעÒâµÄµØ·½£º
1¡¢Èç¹ûÄã²»×¢ÒâµÄÒѾִÐÐÁËdrop²Ù×÷£¬ÄÇÕâÖÖ·½·¨¾Í²»ÄÜÔÙʹÓÃÁË£¬ÒòΪDrop table²Ù×÷»áÊ×ÏȰѱí¶Îת»¯³ÉÁÙʱ¶Î£¬È»ºó¿ªÊ¼ÇåÀíÁÙʱ¶ÎÖеÄextents£¬¼´Ê¹Drop²Ù×÷±»ÖжÏÁË£¬Smon»á½ÓºóÓû§½ø³Ì¼ÌÐøÖ´ÐÐÁÙʱ¶ÎµÄÇåÀí¹¤×÷¡£
2¡¢ÕâÖÖ·½·¨Ö»Êʺϱí¶Î¡£