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ĬÈϵÄÁÙʱ±í¿Õ¼äµÄ£¬²»¹ýÎÒÃÇ¿ÉÒÔͨ¹ýÒÔÏ·½·¨´ïµ½¡£
²é¿´Ä
Ïà¹ØÎĵµ£º
Êý¾Ý¿âʵÀý½á¹¹ ÊÂÎñÒ»ÖÂÐÔ(Transactional Consistency)ºÍ»ùÓÚʱ¼äµãµÄ»Ö¸´(Point-in-time Recovery) ϵͳԪÊý¾Ý
topÊý¾Ý¿âʵÀý½á¹¹
µ±OracleʵÀýÆô¶¯Ö®ºó£¬Ëù¿´µ½µÄ¾ÍÊÇÔÚ·þÎñÆ÷ÄÚ´æÉϵÄÒ»¸ö¸ö²»Í¬ÄÚ´æ¿é¼ÓÉϲúÉúµÄÓëÕâЩÄÚ´æ½»»¥µÄºǫ́½ø³Ì¡£OracleÎĵµ½«ÕâЩÄÚ´æ½á¹¹ºÍ½ø³ÌÊյĺÜÏêϸ¡£
ÓÉOracleʵÀýËùÕ¼ÓõÄÄÚ´æ¿é³ ......
1.´ò¿ªcmd£¬ÊäÈësqlplus£¬»Ø³µ
2.ÊäÈëÓû§Ãû£ºsystem/manager@orcl as sysdba£¬»Ø³µ
3.create user muzai identified by muzai; //´´½¨muzaiÓû§£¬ÃÜÂëΪmuzai
4.grant dba to muzai; //°ÑdbaµÄȨÏÞ¸³¸ømuzaiÕâ¸öÓû§ ......
author:skatetime:2010-05-04
ÔÚÎÒÃǵÄϵͳÀ´ó¼ÒÔÚдpl/sqlʱ£¬´¦Àí¶àÌõ¼Ç¼ʱ£¬¼¸ºõ¶¼ÊÇͨ¹ýÓαêÀ´Íê³ÉµÄ£¬ÕâÑùÊǷdz£Ó°ÏìÐÔÄܵġ£ÎÒÃÇ¿ÉÒÔÓÃÅúÁ¿°ó¶¨¿ÉÒÔ´ó´óµÄ¸ÄÉÆ¡£
ÅúÁ¿°ó¶¨ÊÇoracle9iÔö¼ÓµÄÌØÐÔ£¬ÊÇÖ¸Ö´Ðе¥´Îsql²Ù×÷ÄÜ´«µÝËùÓм¯ºÏÔªËصÄÊý¾Ý¡£Í¨¹ý°ó¶¨°ó¶¨±äÁ¿¿ÉÒÔ¼«´óµÄÌá¸ßÊý¾Ý´¦ÀíËٶȣ¬Ìá¸ßÓ¦ÓÃ³Ì ......
ÒÔÏÂÒÔ½ØͼÐÎʽ¼òÊö¹ØÓÚÔÚIBMСÐÍ»úÉÏ°²×°µ¥»ú°æOracleÊý¾Ý¿âµÄ¹ý³Ì¡£ÒÔ¹©´ó¼Ò²Î¿¼£¬ÒÔ±ã¶ÔÓÚIBMСÐÍ»ú&AIX&Oracle½øÐгõ²½µÄÁ˽⡣ Machine£ºIBM POWER 520 OS&Version£ºAIX_5300-07 Oracle Version:10.2.0.1 ÒÔÏÂÊÇÏêϸ½Øͼ£º 1_OS_Check&Filesets_Check 2_ÀûÓÃsmitty installpÃüÁî½øÈëÈí¼þ£¨°ü£ ......
Temporary TablesÁÙʱ±í
1¼ò½é
ORACLEÊý¾Ý¿â³ýÁË¿ÉÒÔ±£´æÓÀ¾Ã±íÍ⣬»¹¿ÉÒÔ½¨Á¢ÁÙʱ±ítemporary tables¡£ÕâЩÁÙʱ±íÓÃÀ´±£´æÒ»¸ö»á»°SESSIONµÄÊý¾Ý£¬
»òÕß±£´æÔÚÒ»¸öÊÂÎñÖÐÐèÒªµÄÊý¾Ý¡£µ±»á»°Í˳ö»òÕßÓû§Ìá½»commitºÍ»Ø¹örollbackÊÂÎñµÄʱºò£¬ÁÙʱ±íµÄÊý¾Ý×Ô¶¯Çå¿Õ£¬
µ«ÊÇÁÙʱ± ......