ºìÁªLinuxÃÅ»§
Linux°ïÖú

OracleÖÐÓÐЧµÄDropÒ»ÕÅ´ó±í

·¢²¼Ê±¼ä:2006-09-07 00:07:14À´Ô´:ºìÁª×÷Õß:heartweb
Ò»¡¢Á÷³Ì¼ò½é

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¡¢ÕâÖÖ·½·¨Ö»Êʺϱí¶Î¡£
ÎÄÕÂÆÀÂÛ

¹²ÓÐ 0 ÌõÆÀÂÛ