Oracle报错篇 ORA-6544 [pevm_peruws_callback-1] [1000] 超过最大打开游标数问题

        本期将为大家分享“ORA-6544[pevm_peruws_callback-1] [1000]”报错的问题解决方案。

        关键字：”ORA-6544 [pevm_peruws_callback-1] [1000]”、"ORA-01000"、"maximum opencursors exceeded"

        open_cursors动态参数限制每个会话最大的游标数。数据库alert告警日志会出现“ORA-6544 [pevm_peruws_callback-1] [1000]”的报错信息，相关的调用堆栈以“pevm_peruws_callback”结尾，显示内部错误[1000]。由于达到open_cursors最大值限制而生成ORA-6544 错误，查看对应的错误定义：

$ oerr ORA 10000
1000, 00000, "maximum open cursors exceeded"

        登录sys用户或有DBA角色的用户，定位哪个会话打开太多游标以及哪个SQL语句导致“ORA-01000: maximum open cursors exceeded”。

查找特定会话已打开最大游标数和设置的open_cursors参数
SELECT max(a.value) as highest_open_cur, p.value as max_open_cur 
FROM v$sesstata, v$statname b, v$parameter p 
WHERE a.statistic# = b.statistic#  
and b.name = 'opened cursors current' 
and p.name= 'open_cursors' group by p.value;

查找哪些会话打开的游标数最多，导致ORA-01000。
col username for a30
select a.value, s.username, s.sid,s.serial# 
from v$sesstat a
, v$statname b, v$session s 
where a.statistic# = b.statistic#  
and s.sid=a.sid and b.name = 'openedcursors current' 
and s.username is not null anda.value>50;

查找哪些SQL语句占用游标数
select sid ,sql_text, user_name, sql_id,count(*) as "OPEN CURSORS"
from v$open_cursor 
where sid in (177) 
group by sid ,sql_text,user_name,sql_id;

SELECT s.machine, oc.user_name,oc.sql_text, count(1)   
FROM v$open_cursor oc,v$session s
WHERE oc.sid = s.sid and user_name != 'SYS'  
GROUP BY user_name,sql_text, machine  
HAVING COUNT(1) > 5  
ORDER BY count(1) DESC;

        通过调整参数规避问题重现。首先调大动态open_cursors参数值。如果会话没有释放，可以通过重启数据库或清理相应的会话。

SQL> show parameter open_cursor
SQL> alter system set open_cursors = 1000 scope=both;

        OPEN_CURSORS specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once. You can use this parameter to prevent a session from opening an excessive number of cursors.

        OPEN_CURSORS指定单个会话可以同时并发打开 (私有SQL区域的句柄)的最大游标数，可以使用此参数防止会话打开过多的游标。例如，如果将其值设置为1000，则每个会话一次最多可以打开1000个游标。

1、ORA-6544[pevm_peruws_callback-1][604] is caused (Doc ID 2638095.12、Monitoring Open Cursors & TroubleshootingORA-1000 Errors (Doc ID 76684.1)

3、Overview of ORA-1000 Maximum Number of Cursors Exceeded (Doc ID1012266.6)

4、ORA-01000 : Troubleshooting Open Cursors Issues (Doc ID 1477783.1)

5、ORA-6544 [pevm_peruws_callback-1] Error In Alert Log (Doc ID2992686.1)    

6、ORA-6544 [pevm_peruws_callback-1] Detected In Alert Log (Doc ID2530870.1)    

        以上就是本期关于“ORA-6544 [pevm_peruws_callback-1] [1000]”报错的处理方法。希望能给大家带来帮助。