oracle查询数据库游标使用情况，更改游标数量相关sql

----查询游标使用情况以及游标最大数----

SELECT MAX(A.VALUE) AS HIGHEST_OPEN_CUR, P.VALUE AS MAX_OPEN_CUR
FROM V$SESSTAT A, V$STATNAME B, V$PARAMETER P
WHERE A.STATISTIC# = B.STATISTIC#
AND B.NAME = 'opened cursors current'
AND P.NAME = 'open_cursors'
GROUP BY P.VALUE;

---查询游标使用排名-----
select SID,count(*) from v$open_cursor O WHERE O.USER_NAME='TUSER' GROUP BY O.SID
ORDER BY 2 DESC;

-----查询具体游标使用的sql----

select o.sid, q.sql_text  from v$open_cursor o, v$sql q
where q.hash_value=o.hash_value and o.sid =2747;

-----更改游标数量sql----

alter system set open_cursors=2000 scope=both;