ORA-01000 maximum open cursors exceeded-优快云博客

本文介绍了Oracle数据库中关于打开游标的监控与管理方法，包括如何查看当前打开的游标总数、平均数及最大值，并提供了调整参数以优化数据库性能的具体步骤。此外，还给出了通过SQL命令杀死特定会话来释放资源的方法。

Get detail of opened cursors current

select * from gv$sysstat where name like '%open%'

select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur, 
s.username, s.machine
from gv$sesstat a, gv$statname b, gv$session s 
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = 'opened cursors current' 
group by s.username, s.machine
order by 1 desc;

Update open_cursors

show parameter open_cursors;

alter system set open_cursors=1000 scope=MEMORY; (如果启动时候定义了spfile,可以写scope=BOTH)

Kill session to let the parameter take effect

select 
       substr(a.spid,1,9) pid, 
       substr(b.sid,1,5) sid, 
       substr(b.serial#,1,5) ser#, 
       substr(b.machine,1,6) box, 
       substr(b.username,1,10) username, 
--       b.server, 
       substr(b.osuser,1,8) os_user, 
       substr(b.program,1,30) program 
from gv$session b, gv$process a 
where 
b.paddr = a.addr 
and type='USER' 
order by program, sid;

alter system kill session 'SID,SERIAL#'
e.g. alter system kill session '12,2653'

参考
http://superlee0815.blog.163.com/blog/static/10575132120091585954516/
http://blog.itpub.net/9399028/viewspace-695082
https://community.oracle.com/thread/362096

转载于:https://my.oschina.net/l1z2g9/blog/101267