文章版權所有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及報錯代碼給研發,出具更新檔