{"id":443,"date":"2015-09-07T02:52:28","date_gmt":"2015-09-07T02:52:28","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=443"},"modified":"2015-09-07T02:52:28","modified_gmt":"2015-09-07T02:52:28","slug":"oracle-context-size-cursors","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=443","title":{"rendered":"ORACLE CONTEXT SIZE & CURSORS"},"content":{"rendered":"

CONTEXT SIZE & CURSORS<\/span><\/p>\n

   1.
\nContext size <\/span>\uc5d0<\/span> \uad00\ub828\ud55c<\/span> error message<\/span><\/p>\n

     
\n.ora-1046 :can’t acquire space to extend context area.<\/span><\/p>\n

     
\n.ora-1050 :can’t acquire space to open context area.<\/span><\/p>\n

     
\n.ora-1051 :maximum context area extents exceeded.<\/span><\/p>\n

 <\/span><\/p>\n

   2.
\nContext size<\/span>\ub780<\/span> \ubb34\uc5c7\uc778\uac00<\/span>?<\/span><\/p>\n

      (1)
\nCursor<\/span>\uc758<\/span> initial size<\/span>\uc774\ub2e4<\/span> . <\/span>\uc989<\/span>, Cursor <\/span>\uc5d0<\/span>
\nallocate <\/span>\ub418\ub294<\/span> user<\/span> memory<\/span>\uc774\ub2e4<\/span>.<\/span><\/p>\n

      (2) <\/span>\uc774\ub294<\/span> init.ora <\/span>\uc758<\/span> CONTEXT_SIZE <\/span>\uc5d0<\/span> \uc758\ud574<\/span> \uacb0\uc815\ub41c\ub2e4<\/span>.<\/span><\/p>\n

      (3)
\nCursor <\/span>\uc5d0<\/span> \ud560\ub2f9\ub418\ub294<\/span> additional space <\/span>\ub294<\/span>
\nCONTEXT_INCR <\/span>\uc5d0<\/span> \uc758\ud558\uba70<\/span> 50 extents<\/span>\ub97c<\/span> <\/span>\uac16\ub294\ub2e4<\/span>.<\/span><\/p>\n

      (4)
\nRecommended context size increment <\/span>\ub294<\/span> 4096 bytes(4K)<\/span>\uc774\ub2e4<\/span>.<\/span><\/p>\n

      (5)
\nSQL statement<\/span>\uac00<\/span> \uc218\ud589<\/span>
\n\uc2dc\ub9c8\ub2e4<\/span> cursor <\/span>\uac00<\/span> open <\/span>\ub418\uba70<\/span>, <\/span>\uac19\uc740<\/span> cursor<\/span>\uac00<\/span> reuse <\/span>\ub418\ub3c4\ub85d<\/span> design <\/span>\ub418\uc5b4<\/span> SQL*PLUS session<\/span>\uc740<\/span> 2-3 <\/span>\uac1c<\/span>
\n<\/span>\uc774\uc0c1<\/span> open<\/span> <\/span>\ub418\uc5b4\uc9c0\uc9c0<\/span>
\n<\/span>\uc54a\ub294\ub2e4<\/span>.<\/span><\/p>\n

          <\/span>\uadf8\ub7ec\ub098<\/span> SQL*FORMS <\/span>\ub294<\/span> \uc5ec\ub7ec<\/span> \ub2e4\ub978<\/span> task<\/span>\ub97c<\/span> \uc218\ud589\ud558\ubbc0\ub85c<\/span>
\n\ub9ce\uc740<\/span> cursor<\/span>\ub97c<\/span> open<\/span>\ud55c\ub2e4<\/span>.<\/span>  <\/span>(100 or more)<\/span><\/p>\n

     
\n(6)Cursor <\/span>\uac00<\/span> hold <\/span>\ud558\ub294<\/span> item<\/span><\/p>\n

         *
\nthe SQL statement<\/span><\/p>\n

         *
\nthe parsed SQL statement<\/span><\/p>\n

         *
\none row of the result<\/span><\/p>\n

 <\/span><\/p>\n

   3.
\nORA-1051 <\/span>\uc740<\/span> \ubb34\uc5c7\uc774<\/span>
\n\ubb38\uc81c\uc778\uac00<\/span>?<\/span><\/p>\n

      (1)
\ncursor<\/span>\uc758<\/span> size<\/span>\ub97c<\/span> \uc904\uc778\ub2e4<\/span>.<\/span><\/p>\n

      (2)
\nCONTEXT_SIZE, CONTEXT_INCR <\/span>\ub97c<\/span> \ub298\ub9b0\ub2e4<\/span>.<\/span><\/p>\n

 <\/span><\/p>\n

   4.  OPEN_CURSORS <\/span>\uc218\ub97c<\/span>
\n\uc904\uc774\ub294<\/span> \uc804\ub7b5<\/span><\/span><\/p>\n

      (1)
\nCommit<\/span>\uc744<\/span> \uc790\uc8fc\ud55c\ub2e4<\/span>.<\/span><\/p>\n

      (2)
\nSynonym<\/span>\uc774\ub098<\/span> view<\/span>\ub97c<\/span> \uc0ac\uc6a9\ud558\uc9c0<\/span> \uc54a\uc74c\uc73c\ub85c\uc368<\/span> implicit cursor <\/span>\uc218\ub97c<\/span> \uc904\uc778\ub2e4<\/span>.<\/span><\/p>\n

      (3) SQL*FORMS
\n<\/span>\uc5d0\uc11c<\/span> select <\/span>\ubb38\ub300\uc2e0<\/span> #COPY<\/span>\ub85c<\/span>
\n\ubc14\uafd4<\/span> \uc0ac\uc6a9\ud55c\ub2e4<\/span>.<\/span><\/p>\n

      (4) SQL*FORMS
\n<\/span>\uc5d0\uc11c<\/span> large forms<\/span>\ub97c<\/span> \uc5ec\ub7ec<\/span> \uac1c\uc758<\/span>
\n\uc791\uc740<\/span> forms<\/span>\ub85c<\/span>
\n\ub098\ub208\ub2e4<\/span>.<\/span><\/p>\n

      (5) ASAP,
\nEXEC  SQL CLOSE C1; <\/span>\uc744<\/span> \uc218\ud589\ud55c\ub2e4<\/span>.<\/span><\/p>\n<\/p>\n

      (6) HOLD_CURSOR=NO  & RELEASE_CURSOR=YES<\/span>\ub97c<\/span> \uc0ac\uc6a9\ud55c\ub2e4<\/span>.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

CONTEXT SIZE & CURSORS    1. Context size \uc5d0 \uad00\ub828\ud55c error message       .ora-1046 :can’t acquire space to extend context area.       .ora-1050 :can’t acquire space to open context area.       .ora-1051 :maximum context area extents exceeded.      2. Context size\ub780 \ubb34\uc5c7\uc778\uac00?       (1) Cursor\uc758 initial size\uc774\ub2e4 . \uc989, Cursor \uc5d0 allocate \ub418\ub294 user memory\uc774\ub2e4. […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[14],"tags":[208,209],"jetpack_featured_media_url":"","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/443"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=443"}],"version-history":[{"count":0,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/443\/revisions"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=443"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=443"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=443"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}