|
|
[ÀϹÝ] ¿À¶óŬ 8i ORA-00604 ,ORA-04031 ¿¡·¯ ó¸® |
|
È£¼® ´Ô²²¼ ¾´ ±ÛÀÔ´Ï´Ù - 211.¢½.74.31 |
ÀÐÀ½:6233 |
|
|
ERROR:
ORA-00604: error occurred at recursive SQL level 2
ORA-04031: unable to allocate 4048 bytes of shared memory ("shared
pool","TRIGGER$SYS","sga heap","state objects")
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 4048 bytes of shared memory ("shared
pool","unknown object","sga heap","state objects")
¿À¶óŬ 817ÀÇ ¹ö±×¶ó´Â ±º¿©
±×·¡¼ ÆÐÄ¡¸¦ ÇØ¾ß ÇÑ´ä´Ï´Ù...
¾Æ´Ï¸é.. ÆĶó¹ÌÅÍ¿¡
_db_handles_cached = 0
À» Ãß°¡ÇØ ÁÖ¸é ÇØ°áµÈ´Ù´Âµ¥¿ä...
*************************************************************************************************************************
ORA-04031 ¿¡·¯ - 2 ORACLE
2004/12/20 17:05
http://blog.naver.com/simonkbj/40008950855
¿À´Ã ÀÌ ¿¡·¯¶«¿¡ °í»ýÇß´Ù.(2004³â 12¿ù 20ÀÏ)
¿¡·¯ ¿øÀÎÀº ¾Æ·¡·Î ÁüÀ۵ȴÙ.
--> BUG 1397603: ORA-4031 / SGA memory leak of PERMANENT memory occurs for buffer handles. (Workaround: _db_handles_cached=0, Fixed: 8172, 901 )
sga_pool_size´Â 196MB ¿´À¸³ª, ¿¡·¯½Ã »óȲÀº shared pool free memory°¡ 10,056,464¿´°í
shared pool miscellaneousÀº 182,395,936·Î free Memory ºÎÁ·À¸·Î ORA-04031: unable to allocate 4064 bytes of ... ÀÇ ¿¡·¯°¡ °è¼Ó Çؼ ³ª°í ÀÖ¾ú´Ù. °á±¹ Oracle Restart·Î ÀÏ´Ü »óȲÀº ÇØ°áÇßÀ¸³ª patch ¸¦ ÇÏÁö¾Ê´Â ÀÌ»ó ÇÑ´Þ Á¤µµ ÈÄ¿¡ °°Àº ¿¡·¯°¡ ´Ù½Ã ³¯°ÍÀ¸·Î ¿¹»óµÈ´Ù. shared pool miscellaneous °¡ °è¼ÓÇؼ Áõ°¡ ÇÏ°í ÀÖ´Â »óȲÀÌ´Ù. ´ÙÀ½ ¿¡·¯½Ã _db_handles_cached=0 ¿É¼ÇÀ» Àû¿ëÇØ º¼ ¿¹Á¤ÀÌ´Ù. T.T (ÆøźÀ» µé°í °¡¾ßÇϳª...)
¾Æ·¡´Â ORA-04031¿¡·¯¿¡ ´ëÇÑ ³»¿ë
Ãâó : http://oracle.mania.co.kr/board/content.asp?code=tuning&number=2&page=1&ref=2&startpage=1
----------------------------------------------------------------------------------------
linuxµç aixµç
oracle 8.1.7¿¡¼
°£È¤ ORA-04031 À̶õ error¸¦ Á¢ÇßÀ» ¼ö ÀÖ½À´Ï´Ù.
+++++++++++++++++++++++++++++++++
ORA-04031: unable to allocate 4096 bytes of shared
memory ("shared pool","....
Error accessing package DBMS_APPLICATION_INFO
ERROR:
ORA-04031: unable to allocate 4096 bytes of shared
memory ("shared pool","BEGIN ....
+++++++++++++++++++++++++++++++++
ÀÌ·± °æ¿ì¿¡ ¸î°¡Áö °ËÅäÇØ ºÁ¾ß ÇÕ´Ï´Ù.
Á¤¸»·Î shared_pool size°¡ ÀÛÀº °ÍÀÎÁö....
ÀÛÁö ¾ÊÀºµ¥µµ ÀÌ·± ¹®Á¦°¡ ¹ß»ýÇÑ´Ù¸é
ÀÌ´Â oracle ÀÚüÀÇ bugÀ¸·Î º¸¼Å¾ß ÇÕ´Ï´Ù.
Áï 8.´ë¿¡¼ 8i·Î ³Ñ¾î°¡¸é¼ block handle ¹æ½ÄÀÌ ¹Ù²î¸é¼
8i¿¡¼´Â shÂÊ¿¡¼ »ç¿ëÇÑ blockÀ» release½ÃÅ°Áö ¾Ê´Â
¿À·ù°¡ ÀÖ¾î¼ ºÎÁ·ÇÏ´Ù´Â ¸Þ¼¼Áö°¡ ³ª¿À°Ô µË´Ï´Ù.
À̸¦ À§Çؼ±
ÃÖ¼Ò 8.1.7.2 ·Î patch¸¦ Àû¿ëÇÏ´Â°Ô Á¦ÀÏ ÁÁ°í,
ÀÌ°Ô ¹«¸®¼ö¶ó¸é
_db_handles_cached=0
¸¦ ÀÌ¿ëÇϸé block handle¹æ½ÄÀ» 8.´ëÀÇ ¹æ½ÄÀ¸·Î ÀüȯÇÏ´Â
°ÍÀ¸·Î ÀÌÇØÇÏ¸é µË´Ï´Ù¸¸ release¹®Á¦¸¦ ÇØ°áÇÒ¼ö ÀÖ½À´Ï´Ù.
ÀÌ·Î ÀÎÇÑ ¼º´É ÀúÇÏ´Â ¾ÆÁ÷ º¸°íµÈ¹Ù ¾ø´Ù°í ÇÕ´Ï´Ù.
Àú´Â linux¿¡¼ 2001³âµµ¿¡ ¹ß»ýÇØ Àû¿ëÇؼ
¾ÆÁ÷ ÀçºÎÆà Çѹø¸¸ ÇÒ Á¤µµ·Î ¾ÈÁ¤ÀûÀ¸·Î ¿î¿µÇÏ°í ÀÖ°í
¿À´Ã aix¿¡¼µµ ¹ß»ýÇÏ¿© Àû¿ëÇß½À´Ï´Ù.
************************************************************************************
ORA-04031 ¿¡·¯ : ¸Þ¸ð¸® ´ÜÆíÈ | [Q&A]µ¥ÀÌÅͺ£À̽º 2005.09.21 11:56
do82ng http://cafe.naver.com/dbaclub/4
ERROR:
ORA-00604: error occurred at recursive SQL level 2
ORA-04031: unable to allocate 4048 bytes of shared memory ("shared
pool","TRIGGER$SYS","sga heap","state objects")
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 4048 bytes of shared memory ("shared
pool","unknown object","sga heap","state objects")
DB¸¦ °ü¸®ÇÏ¸é¼ ´ÙÀ½°ú °°Àº ¿¡·¯¸¦ Á÷¸éÇÒ¶§°¡ ÀÖ´Àµ¥ ÀÌ·² °æ¿ì¿¡´Â ½Å¼ÓÇÏ°Ô
´ëóÇÒ ¼ö ÀÖ´Â ¹æ¹ý
1. ¿À·¡µÈ ¼¼¼ÇÀ» ²÷¾î¼ shared pool ¿¡ Àâ°í ÀÖ´Â ¸Þ¸ð¸®¸¦ ÇØÁ¦ ½ÃÄÑÁØ´Ù.
2. alter system flush shared_pool; ¸í·É¾î¸¦ ÅëÇØ ¸Þ¸ð¸® Á¶°¢¸ðÀÓÀ» ÇØÁØ´Ù.
Àå±âÀûÀÎ ´ëÃ¥Àº
´ÜÆíÈ ½ÃÅ°´Â SQL¸¦ ã¾Æ³»°í ¹ÙÀεù º¯¼öÈ ½ÃŲ´Ù.
ORA-04031 ÀÇ °æ¿ì shared pool ³»¿¡ ¸Þ¸ð¸® Á¶°¢È¿¡ µû¶ó¼ ¿¬¼ÓµÈ parsing °ø°£À» Á¦°øÇÏÁö ¸øÇϱ⠶§¹®¿¡ ¹ß»ýÇÏ´Â ¿¡·¯ÀÔ´Ï´Ù.
´Ù½Ã ¸»Çؼ parsing ¿¡·¯°¡ ¹ß»ýÇÏ´Â °ÍÀÔ´Ï´Ù.
À̸¦ ÇØ¼Ò ÇØÁÖ±â À§ÇÑ ¹æ¹ýÀ¸·Î
°³Ã¼°¡ Å« ÀÚÁÖ »ç¿ëµÇ´Â ÇÁ·Î½ÃÁ®µîÀ» ¸Þ¸ð¸®¿¡ pined ÇØÁÖ¸é µÇ¸ç,
shared pool »çÀÌÁ ´Ã·Á Áִ°ÍÀÌ °¡Àå ÁÁÀ¸¸ç,
»óȲÀÌ ¿©ÀÇÄ¡ ¾Ê´Â °æ¿ì ´ÜÆíÈµÈ shared pool ÀÇ Á¶°¢ÈÀ» ´Ù½Ã flush ÇØÁÖ´Â ¹æ¹ý
µîÀÌ ÀÖ½À´Ï´Ù.
SQL> alter system flush shared_pool;
shared pool ÀÇ hit À²À» Àß ºÐ¼®ÇØ º¸½Ã°í ¿À¶óŬÀÇ ±ÇÀå»çÇ׿¡ µû¶ó¼ Æ©´× °¡ÀÌµå ¶óÀÎÀ» Á¤ÇÏ´Â°Ô Áß¿äÇÕ´Ï´Ù.
³Ê¹« Å©°Ô Á༠free size °¡ ³Ê¹« ¸¹ÀÌ ³²´Â °æ¿ì Áï hit À²Àº ÁÁÀºµ¥, free size °¡ ³Ê¹« Å« °æ¿ì´Â ¸Þ¸ð¸® ³¶ºñ¸¦ ÇÏ°Ô µÇ¸ç, °¢°¢ÀÇ o/s ¿¡ µû¶ó¼ paging À̳ª swap ÀÌ ¹ß»ýÇÒ °¡´É¼ºÀÌ ÀÖÀ¸´Ï, Æ©´×ÈÄ ÀûÀûÇÒ ¸ð´ÏÅ͸¦ ÅëÇؼ »çÀÌÁ Àâ¾Æ °¡´Â °ÍÀÌ ÁÁ½À´Ï´Ù.
SQL> select name, bytes/1024/1024 "Size in MB" from v$sgastat where name='free memory';
NAME Size in MB
-------------------------- ----------
free memory 451.496498
free memory .5859375
free memory .03125
Áï ¿À¶óŬÀÇ µ¿Àû ºäµéÀÇ ´ëºÎºÐÀº current ÇÑ ³»¿ëÀÌ ¾Æ´Ñ ÃàÀû¿ëÀ¸·Î Æò±ÕÄ¡¸¦ ³ªÅ¸ ³»±â ¶§¹®¿¡ Àǹ̰¡ ¾ø½À´Ï´Ù.
|
|
|
|
|
|