ORACLE ÁÙʱ±í¿Õ¼äʹÓÃÂʹý¸ßµÄÔÒò¼°½â¾ö·½°¸
ÔÚÊý¾Ý¿âµÄÈÕ³£Ñ§Ï°ÖУ¬·¢ÏÖ¹«Ë¾Éú²úÊý¾Ý¿âµÄĬÈÏÁÙʱ±í¿Õ¼ätempʹÓÃÇé¿ö´ïµ½ÁË30G£¬Ê¹ÓÃÂÊ´ïµ½ÁË100%£»´ýµ÷ÕûΪ32Gºó£¬Ê¹ÓÃÂÊ»¹ÊÇΪ100%£¬µ¼Ö´ÅÅÌ¿Õ¼äʹÓýôÕÅ¡£¸ù¾ÝÁÙʱ±í¿Õ¼äµÄÖ÷ÒªÊǶÔÁÙʱÊý¾Ý½øÐÐÅÅÐòºÍ»º´æÁÙʱÊý¾ÝµÈÌØÐÔ£¬´ýÖØÆôÊý¾Ý¿âºó£¬temp»á×Ô¶¯ÊÍ·Å¡£ÓÚÊÇÏëͨ¹ýÖØÆôÊý¾Ý¿âµÄ·½Ê½À´»º½âÕâÖÖÇé¿ö£¬µ«ÊÇÖØÆôÊý¾Ý¿âÖ®ºó£¬·¢ÏÖÁÙʱ±í¿Õ¼ätempµÄʹÓÃÂÊ»¹ÊÇ100%£¬Ò»µãû±ä¡£ËäÈ»ÔËÐÐÖÐÓ¦ÓÃÔÝʱûÓб¨Ê²Ã´´íÎ󣬵«ÊÇÕâÔÚÒ»¶¨³Ì¶ÈÉÏ´æÔÚÒ»¶¨µÄÒþ»¼£¬Óдý½â¾ö¸ÃÎÊÌâ¡£ÓÉÓÚÁÙʱ±í¿Õ¼äÖ÷ҪʹÓÃÔÚÒÔϼ¸ÖÖÇé¿ö£º
1¡¢order by or group by (disc sortÕ¼Ö÷Òª²¿·Ö)£»
2¡¢Ë÷ÒýµÄ´´½¨ºÍÖØ´´½¨£»
3¡¢distinct²Ù×÷£»
4¡¢union & intersect & minus sort-merge joins£»
5¡¢Analyze ²Ù×÷£»
6¡¢ÓÐЩÒì³£Ò²»áÒýÆðTEMPµÄ±©ÕÇ¡£
OracleÁÙʱ±í¿Õ¼ä±©ÕǵÄÏÖÏó¾¹ý·ÖÎö¿ÉÄÜÊÇÒÔϼ¸¸ö·½ÃæµÄÔÒòÔì³ÉµÄ£º
1. ûÓÐΪÁÙʱ±í¿Õ¼äÉèÖÃÉÏÏÞ£¬¶øÊÇÔÊÐíÎÞÏÞÔö³¤¡£µ«ÊÇÈç¹ûÉèÖÃÁËÒ»¸öÉÏÏÞ£¬×îºó¿ÉÄÜ»¹ÊÇ»áÃæÁÙÒòΪ¿Õ¼ä²»¹»¶ø³ö´íµÄÎÊÌ⣬ÁÙʱ±í¿Õ¼äÉèÖÃ̫С»áÓ°ÏìÐÔÄÜ£¬ÁÙʱ±í¿Õ¼ä¹ý´óͬÑù»áÓ°ÏìÐÔÄÜ£¬ÖÁÓÚÐèÒªÉèÖÃΪ¶à´óÐèÒª×ÐϸµÄ²âÊÔ¡£
2.²éѯµÄʱºòÁ¬±í²éѯÖÐʹÓõıí¹ý¶àÔì³ÉµÄ¡£ÎÒÃÇÖªµÀÔÚÁ¬±í²éѯµÄʱºò£¬¸ù¾Ý²éѯµÄ×ֶκͱíµÄ¸öÊý»áÉú³ÉÒ»¸öµÏ˹¿¨¶û»ý£¬Õâ¸öµÏ˹¿¨¶û»ýµÄ´óС¾ÍÊÇÒ»´Î²éѯÐèÒªµÄÁÙʱ¿Õ¼äµÄ´óС£¬Èç¹û²éѯµÄ×ֶιý¶àºÍÊý¾Ý¹ý´ó£¬ÄÇô¾Í»áÏûºÄ·Ç³£´óµÄÁÙʱ±í¿Õ¼ä¡£
3.¶Ô²éѯµÄijЩ×Ö¶ÎûÓн¨Á¢Ë÷Òý¡£OracleÖУ¬Èç¹û±íûÓÐË÷Òý£¬ÄÇô»á½«ËùÓеÄÊý¾Ý¶¼¸´ÖƵ½ÁÙʱ±í¿Õ¼ä£¬¶øÈç¹ûÓÐË÷ÒýµÄ»°£¬Ò»°ãÖ»Êǽ«Ë÷ÒýµÄÊý¾Ý¸´ÖƵ½ÁÙʱ±í¿Õ¼äÖС£
Õë¶ÔÒÔÉϵķÖÎö£¬¶Ô²éѯµÄÓï¾äºÍË÷Òý½øÐÐÁËÓÅ»¯£¬Çé¿öµÃµ½»º½â£¬µ«ÊÇÐèÒª½øÒ»²½²âÊÔ¡£
×ܽ᣺
1.SQLÓï¾äÊÇ»áÓ°Ïìµ½´ÅÅ̵ÄÏûºÄµÄ£¬²»µ±µÄÓï¾ä»áÔì³É´ÅÅ̱©ÕÇ¡£
2.¶Ô²éѯÓï¾äÐèÒª×ÐϸµÄ¹æ»®£¬²»ÒªÏ뵱ȻµÄÈ¥¶¨ÒåÒ»¸ö²éѯÓï¾ä£¬ÌرðÊÇÔÚ¿ÉÒÔÌṩÓû§×Ô¶¨Òå²éѯµÄÈí¼þÖС£
3.×Ðϸ¹æ»®±íË÷Òý¡£Èç¹ûÁÙʱ±í¿Õ¼äÊÇtemporaryµÄ£¬¿Õ¼ä²»»áÊÍ·Å£¬Ö»ÊÇÔÚsort½áÊøºó±»±ê¼ÇΪfreeµÄ£¬Èç¹ûÊÇpermanentµÄ£¬ÓÉSMON¸ºÔðÔÚsort½áÊøºóÊÍ·Å£¬¶¼²»ÓÃÈ¥ÊÖ¹¤Êͷŵġ£²é¿´ÓÐÄÄЩÓû§ºÍSQLµ¼ÖÂTEMPÔö³¤µÄÁ½¸öÖØÒªÊÓͼ£ºv$sort_usageºÍv$sort_segment¡£
ͨ¹ý²éѯÏà¹ØµÄ×ÊÁÏ£¬·¢ÏÖ½â¾ö·½°¸ÓÐÈçϼ¸ÖÖ£º
Ò»¡¢Öؽ¨ÁÙʱ±í¿Õ¼ätemp
Temporary tablespaceÊDz»ÄÜÖ±½ÓdropĬÈϵÄÁÙʱ±í¿Õ¼äµÄ£¬²»¹ýÎÒÃÇ¿ÉÒÔͨ¹ýÒÔÏ·½·¨´ïµ½¡£
²é¿´Ä
Ïà¹ØÎĵµ£º
Æô¶¯ÓëÅäÖòÎÊý ¸ü¸ÄϵͳԪÊý¾ÝºÍ²ÎÊýÅäÖà ¶¯Ì¬ÊÓͼ ´æ´¢µÄÎïÀíºÍÂß¼½á¹¹ Æô¶¯ºÍ¹Ø±Õ
topÆô¶¯ÓëÅäÖòÎÊý
ÔÚSQL Server°²×°µÄʱºò£¬Í¬Ê±Ò²»áÍùWindows×¢²á±íÀïÃæÌí¼ÓһЩ¼Ç¼£¬ÕâЩע²á±í¼üÖµÖ¸¶¨ÁËʵÀýËùÐèÒªµÄ¸÷ÖÖ²ÎÊý¡£±ÈÈ磬ÓеÄ×¢²á±í¼üÖµÖ¸¶¨ÁË´íÎóÈÕÖ¾µÄ±£´æĿ¼£¬ÁíÍâµÄÖ¸¶¨ÁËĬÈϵı¸·ÝĿ¼µÈµÈ¡£SQL ServerÔÚÔ ......
ʹÊÔÒò:
1.ÓÉÓÚÎó²Ù×÷ÓÃhp unix ÃüÁî rm -f datafilename ɾ³ý±í¿Õ¼äµÄÊý¾ÝÎļþ
2.alter tablespace tablespacenaem drop datafile datafile ;
3.drop tablespace tablespacename including content and datafiles;
ÉÏÊöÁ½¸ö²½ÖèÎÒÓÃÁ˽üÈý¸öСʱ¶¼Ã»ÓÐÖ´ÐÐÍ꣬×îºóµ¼ÖÂÊý¾Ý¿âå´»ú¡£ÏÂÃæ°ÑÎÒµ±Ê±Æô¶¯Êý¾ÝµÄºóÌ¨Ò ......
²é¿´µ±Ç°Óû§µÄȱʡ±í¿Õ¼ä
SQL>select username,default_tablespace from user_users;
²é¿´µ±Ç°Óû§µÄ½ÇÉ«
SQL>select * from user_role_privs;
²é¿´µ±Ç°Óû§µÄÏµÍ³È ......
¹ØÓÚOracleÊÂÎñµÄ×ܽá
1.ʲôÊÇÊÂÎñ£¬ÊÂÎñµÄÌØÐÔÊÇʲô£¿
ÊÂÎñµÄÈÎÎñ±ãÊÇʹÊý¾Ý¿â´ÓÒ»ÖÖ״̬±ä»»³ÉΪÁíÒ»ÖÖ״̬£¬ÕⲻͬÓÚÎļþϵͳ£¬ËüÊÇÊý¾Ý¿âËùÌØÓõġ£ËüµÄÌØÐÔÓÐËĸö£ºTOM×ܽáΪACID¼´
Ô×ÓÐÔatomicity:Óï¾ä¼¶Ô×ÓÐÔ£¬¹ý³Ì¼¶Ô×ÓÐÔ£¬ÊÂÎñ¼¶Ô×ÓÐÔ
Ò»ÖÂÐÔconsistency:״̬һÖ£¬Í¬Ò»ÊÂÎñÖв»»áÓÐÁ½ÖÖ״̬
¸ôÀëÐÔiso ......
Oracle Data Guard, ·ÖÂß¼StandbyºÍÎïÀíStandby¡£ ÏÂÃæ½²µÄÊÇÎïÀíStandby »·¾³µÄ´î½¨²½Öè¡£ ÓйØData GuardµÄһЩ¸ÅÄîÐÔµÄÀíÂÛ֪ʶ£¬Çë²Î¿¼ÎÒµÄblog£¬ ÕâÀï²»×ö¹ý¶àµÄ˵Ã÷¡£
Oracle Data Gurad ÀíÂÛ֪ʶ
http://blog.csdn.net/tianlesoftware/archive/2010/04 ......