oracle怎麼查詢遊標

查詢方法：1、用「select count(*) from v$open_cursor」；2、用「SELECT A.USER_NAME,COUNT(*) FROM V$OPEN_CURSOR A GROUP BY A.USER_NAME」。

本教學操作環境：Windows7系統、Oracle 11g版、Dell G3電腦。

1、檢視系統遊標數(最大遊標數)

select value from v$parameter where name = 'open_cursors';show parameter open_cursors;

2、檢視當前開啟的遊標數目

select count(*) from v$open_cursor;

3、檢視遊標使用情況

select o.sid, osuser, machine,o.sql_id,o.sql_text,o.cursor_type, count(*) num_curs from v$open_cursor o, v$session s where user_name = 'GLOGOWNER' and o.sid = s.sid group by o.sid, osuser, machine,o.sql_id,o.sql_text,o.cursor_type order by num_curs desc;

4、修改Oracle最大遊標數

根據遊標佔用情況分析存取資料庫的程式在資源釋放上是否正常,如果程式釋放資源沒有問題，則加大遊標數。

alter system set open_cursors=2000 scope=both;

5、各使用者的開啟遊標總數

SELECT A.USER_NAME, COUNT(*) FROM V$OPEN_CURSOR A GROUP BY A.USER_NAME;

6、查詢資料庫各使用者各個終端的快取遊標數

SELECT AA.USERNAME, AA.MACHINE, SUM(AA.VALUE) FROM (SELECT A.VALUE, S.MACHINE, S.USERNAME FROM V$SESSTAT A, V$STATNAME B, V$SESSION S WHERE A.STATISTIC# = B.STATISTIC# AND S.SID = A.SID AND B.NAME = 'session cursor cache count') AA GROUP BY AA.USERNAME, AA.MACHINE ORDER BY AA.USERNAME, AA.MACHINE;

7、查詢資料庫各使用者各個終端的開啟遊標數

SELECT AA.USERNAME, AA.MACHINE, SUM(AA.VALUE) FROM (SELECT A.VALUE, S.MACHINE, S.USERNAME FROM V$SESSTAT A, V$STATNAME B, V$SESSION S WHERE A.STATISTIC# = B.STATISTIC# AND S.SID = A.SID AND B.NAME = 'opened cursors current') AA GROUP BY AA.USERNAME, AA.MACHINE ORDER BY AA.USERNAME, AA.MACHINE;