SSH框架中,服务器长时间运行后报“打开游标超出最大值”的错误。网上有说
connection.createstatement()和connection.preparestatement()打开游标后没有及时关闭的原因,但是我的程序里并没写jdbc代码。而是public class CommonBaseDaoHib extends HibernateDaoSupport,用这个CommonBaseDaoHib做数据的增删改查。高手教我~
connection.createstatement()和connection.preparestatement()打开游标后没有及时关闭的原因,但是我的程序里并没写jdbc代码。而是public class CommonBaseDaoHib extends HibernateDaoSupport,用这个CommonBaseDaoHib做数据的增删改查。高手教我~
解决方案 »
- 把表空间tbsp从现在的50g,改成25g
- MS SQL转ORACLE问题,新手请教,谢谢。
- 不安装oracle客户端访问服务器oracle,要怎么做???
- 【急求】如何快速迁移带有clob大对象的数据
- startup 实例和alter database open有什么区别?alter database mount安装数据库是怎么安装啊?
- 你们快点帮帮我啦
- 谁碰上过ORA-07446错误没有?
- 各位高手看过来。我如何写这么个简单的SQL语句?
- 在SVRMGR中关闭数据库出现问题?
- 救命!!--oracle9i install on linux 7.2
- 如何优化这句SQL?执行相当慢,,,
- 求助一条行转列的查询语句
查看open_cursors是多少
show parameter open_cursors;
然后
alter system set open_cursors=当前值+50 scope=spfile;
最后重启系统
startup
我不太懂。
每个session打开的游标数目,lz也许可以观察你的这个表看是数目sql打开的游标过多,优化下,呵呵。。可能记录比较多
select * from v$open_cursor
pm.monitor_year || '-' || pm.monitor_month || '-' ||decode(pm.monitor_ten_days, '01','上旬','02','下旬') as monitortime,
decode(pm.biotope, '01', '人房', '02', '畜圈') biotopes,
z.cnname as county,
pm.report_unit,
coalesce(pmi1.dskw , 0) dskw,
coalesce(pmi1.sdykw , 0) sdykw,
coalesce(pmi1.zhaw , 0) zhaw,
coalesce(pmi1.zjkw , 0) zjkw,
coalesce(pmi1.sraw , 0) sraw,
coalesce(pmi1.bwyw , 0) bwyw,
coalesce(pmi1.qt , 0) qt,
(select [ORGCODE] from dual) orgcode
from pidera_mosquito pm
left join (select pmi.mosquito_reference_id,
sum(coalesce(decode(pmi.mosquito_name, '01', pmi.mosquito_num),0)) dskw,
sum(coalesce(decode(pmi.mosquito_name, '02', pmi.mosquito_num),0)) sdykw,
sum(coalesce(decode(pmi.mosquito_name, '03', pmi.mosquito_num),0)) zhaw,
sum(coalesce(decode(pmi.mosquito_name, '04', pmi.mosquito_num),0)) zjkw,
sum(coalesce(decode(pmi.mosquito_name, '05', pmi.mosquito_num),0)) sraw,
sum(coalesce(decode(pmi.mosquito_name, '06', pmi.mosquito_num),0)) bwyw,
sum(coalesce(decode(pmi.mosquito_name, '07', pmi.mosquito_num),0)) qt
from pidera_mosquito_info pmi
group by pmi.mosquito_reference_id) pmi1
on (pm.mosquito_id = pmi1.mosquito_reference_id)
join zonecode z
on z.zonecode = pm.zonecode
where pm.del_state != '1'
and [DISTRICT]
and [ZONECODE]
and [STARTDATE]
and [ENDDATE]
这样一条sql怎么优化?实在找不到原因了。
普通sql的游标oracle会自动关闭.
检查下程序中打开的cursor是否有及时close
这时候就要使用close cursorname在使用完游标后关闭游标.如果没关闭,那么多运行几次就会超出oracle对每个session的open_cursor上限.
这里查询出来的sql不都是打开的sql吗?
SELECT *
FROM v$open_cursor t
WHERE t.sid IN
(SELECT t.sid
FROM v$open_cursor t
GROUP BY t.sid
HAVING COUNT(*) >= (SELECT VALUE
FROM v$parameter t
WHERE t.NAME = lower('OPEN_CURSORS')));