游标

Oracle数据库默认open_cursor的数为每个会话50,一般我们会上调该值到500

-------查询数据库打开的游标数
SELECT * FROM SYS.V_$SQL_BIND_DATA  ;
select * from v$parameter where name ='open_cursors';

-----查看数据库参数设置,每个会话的最多打开游标数
show parameters open_cursors
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     500

可以到init.ora中修改OPEN_CURSORS

select *  from v$open_cursor;
select count(*)  from v$open_cursor;

 

----获取打开的游标数。下面的查询按降序每个用户为每个会话打开的游标数。 
select o.sid, osuser,user_name, machine, count(*) num_curs
from v$open_cursor o, v$session s
where  o.sid=s.sid
group by o.sid, osuser,user_name, machine
order by  num_curs;

-----查看语句
SELECT b.sql_text
FROM v$session a,v$sqltext b
WHERE a.sql_hash_value=b.hash_value AND a.sid=&SID ORDER BY piece;