關於ORA-01000: maximum open cursors exceeded＂ 問題分析總結

關於ora-01000：超出最大可開啟的遊標數 的一點理解
 ORA-01000: maximum open cursors exceeded"怎麼解決
 ERROR at line 1:
 ORA-01000: maximum open cursors exceeded

問題分析：
 1.遊標open後，出錯了又沒有close.
 2.可能是表結構的問題。
 
解決方案：
 step 1:
    查看資料庫當前的遊標數配置
    show parameter open_cursors;
 

step 2:
    查看遊標使用方式：
 select o.sid, oSUSEr, machine, count(*) num_curs
 from v$open_cursor o, v$session s
 where user_name = 'user' and o.sid=s.sid
 group by o.sid, osuser, machine
 order by  num_curs desc;
 
此處的user_name='user'中,user代表佔用資料庫資源的資料庫使用者名稱.
 

step 3:
    查看遊標執行的sql情況：
 select o.sid q.sql_text
 from v$open_cursor o, v$sql q
 where q.hash_value=o.hash_value and o.sid = 123;
 
step 4:
    根據遊標佔用情況分析訪問資料庫的程式在資源釋放上是否正常,如果程式釋放資源沒有問題，則加大遊標數。
    alter system set open_cursors=2000 scope=both;
   
 以上為cursors數確實不夠的情況下的處理方法。
 但在有的時候，就算是加大了open_cursor 。譬如，我將遊標大小改到了300，還是出現錯誤，改到1000以後開始出現了ORA-01001:invalid cursors。再怎麼加大
 open_cursors的數量都無濟於事。碰到這種情況極可能就是表結構的問題了。
 

 

那麼如何來驗證呢？
 案例來自itpub，yangtingkun：
 “我們系統的資料量比較大，近200張表，有些表一天要插入1000000條左右的資料。表是使用ER/STDIO設計的，然後直接產生建表的指令碼，由於沒有設定physical_attributes_clause語句中的建表參數，因此使用了預設的參數。好像是INITIAL 10K NEXT 10K PCTFREE 20 PCTUSED 50由於表的儲存空間太小，在
 很短的時間內就會裝滿，因此Oracle就需要不停為30多張表（資料量較大的）申請空間。於是在程式運行了一段時間後，開始出現ORA-01000: maximum open
 cursors exceeded的錯誤。 我將遊標大小改到了300，還是出現錯誤，改到1000以後開始出現了ORA-01001:invalid cursors。再怎麼加大open_cursors的數量都
 無濟於事。 使用select * from v$open_cursors查詢，發現有幾百條的INSERT語句遊標沒有釋放。
 
開始懷疑是程式調用的問題，仔細檢查程式沒有發現問題。但是發現對某張表進行TRUNCATE操作後，對此表進行插入的遊標全部釋放，於是開始懷疑是表結構本
 身的問題。重新設定了建表參數，將資料量最大的表的INITIAL和NEXT均設定為50M。至今未再出現同樣的錯誤。”
 

FYI：
 首先通過v$open_cursor中的SQL_TEXT欄位可以查出沒有釋放的SQL是大致是什麼語句。
 由於建表參數設定不當，會有很多的INSERT語句無法釋放。
 

 

SELECT * FROM V$OPEN_CURSOR WHERE SQL_TEXT LIKE 'INSERT％‘
 
找一找哪些表的INSERT語句沒有釋放的遊標比較多。從ALL_TABLES裡查看該表的建表參數是否合適。
 

yangtingkun的分析：
 “開始時表參數
 PCT FREE 10
 PCT USED 40
 STORAGE(INITIAL 10K
        NEXT 10K
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 0
        )
 9K的空間，以我們的系統幾秒鐘就塞滿了，下一條插入語句來的時候ORACLE要去自動申請空間，該語句所佔用的遊標暫時無法釋放。ORACLE申請的空間馬上又被
 塞滿，又要去申請新的空間。如果二、三十多張表在幾個鐘頭內都不停要求ORACLE去進行上述的操作，估計會造成大量的遊標來不及釋放。以上只是我的估計。”

1 2 下一頁