【干货攻略】ORA-6544 [pevm_peruws_callback-1] [1000] 超过最大打开游标数问题

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

查找特定会话已打开最大游标数
SELECT  max(a.value) as highest_open_cur, 
        p.value as max_open_cur 
 FROM v$sesstat a, 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 = 'opened cursors current' 
and s.username is not null and a.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;

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