oracle打开游标最大数是多少,ORA-01000: 超出打开游标的最大数

文章版权所有Jusin Hao(luckyfriends)，支持原创，转载请注明。

1.1.1. 问题现象

系统报‘超出游标最大数’错误

vo.fa.pub.exp.FABusinessException: ORA-01000: 超出打开游标的最大数

1.1.2. 查找游标溢出的方法：

-----按游标数排列session

select o.sid, osuser, machine, count(*) num_curs

from v$open_cursor o, v$session s

where user_name = 'ZMSJ' and o.sid=s.sid

group by o.sid, osuser, machine

order by num_curs desc;

---查询游标数大于一定值(这里是65)的session

select sid, osuser, machine, num_curs from (

select o.sid, osuser, machine, count(*) num_curs

from v$open_cursor o, v$session s

where o.sid=s.sid

group by o.sid, osuser, machine

order by num_curs desc)

where num_curs > '65'

----1)查询指定sid的sql内容

select q.sql_text

from v$open_cursor o, v$sql q

where q.hash_value=o.hash_value and o.sid = 146;

----2)查询指定sid的sql内容

select q.sql_text,q.SQL_FULLTEXT,q.SQL_ID,v.SQL_HASH_VALUE,

v.CLIENT_INFO,

v.SQL_ADDRESS,

v.MACHINE,

v.TERMINAL

from v$open_cursor o, v$sql q,v$session v

where q.hash_value=o.hash_value

AND q.ADDRESS = v.SQL_ADDRESS

and v.sid = 40;

1.1.3. 问题解决：

提交查出的sql及报错代码给研发，出具补丁