2013-01-10

ORA-01000 maximum open cursors exceeded

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

Oracle