Oracle mos文档关于视图v$open_cursor中矛盾说法
How to Monitor and tune Open and Cached Cursors (文档 ID 1430255.1)中指出：
v$open_cursor shows cached cursors, not currently open cursors, by session. If you are wondering how many cursors a session has open, do not look in v$open_cursor. It shows the cursors in the session cursor cache for each session, not cursors that are actually open.
谷歌翻译：v$open_cursor 按会话显示缓存的游标，而不是当前打开的游标。如果您想知道一个会话打开了多少个游标，请不要查看 v$open_cursor。它显示每个会话的会话游标缓存中的游标，而不是实际打开的游标。
V$OPEN_CURSOR HAVING SQL_IDs WHICH DO NOT EXIST IN V$SQLAREA (文档 ID 838321.1)中指出：
Basically v$open_cursor view lists cursors that each user session currently has opened and parsed.
谷歌翻译：基本上 v$open_cursor 视图列出了每个用户会话当前已打开和解析的游标。
1430255.1一文中表示v$open_cursor只是显示了缓存的游标，而不是当前打开的游标，实际上在官档对V$OPEN_CURSOR的字段CURSOR_TYPE就有说明：
Type of cursor:
OPEN PL/SQL- Open PL/SQL cursors
OPEN- Other open cursors
SESSION CURSOR CACHED- Cursors cached in the generic session cursor cache
OPEN RECURSIVE- Open recursive cursors
DICTIONARY LOOKUP CURSOR CACHED- Cursors cached in the dictionary lookup cursor cache
BUNDLE DICTIONARY LOOKUP CACHED- Cursors cached in the bundled dictionary lookup cursor cache
JAVA NAME TRANSLATION CURSOR CACHED- Cursors cached in the Java name translation cursor cache
REPLICATION TRIGGER CURSOR CACHED- Cursors cached in the replication trigger cursor cache
CONSTRAINTS CURSOR CACHED- Cursors cached in the constraints cursor cache
PL/SQL CURSOR CACHED- Cursors cached in the PL/SQL cursor cache
结合838321.1看，838321.1的说法更加准确，v$open_cursor 视图列出了每个用户会话当前已打开和解析的游标。
做个简单的实验，打开两个会话sid171,sid321：
variable x refcursor; variable z refcursor; variable b refcursor; variable a refcursor; exec open :x for select * from dual; exec open :z for select * from dual; exec open :b for select * from dual; exec open :a for select * from dual;
--sid321查看sid171初始化信息： 16:39:52 SYS@zkmdb(321)> col user_name for a10 16:39:52 SYS@zkmdb(321)> col CURSOR_TYPE for a35 16:39:52 SYS@zkmdb(321)> col sql_text for a60 16:39:52 SYS@zkmdb(321)> select * from v$open_cursor where sid=171; SADDR SID USER_NAME ADDRESS HASH_VALUE SQL_ID SQL_TEXT LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE ---------------- --- ---------- ---------------- ---------- --------------- ------------------------------------------------------------ ------------------- ----------- ----------------------------------- 0000000A5D197FC8 171 SYS 0000000A5903FDC0 1950821498 459f3z9u4fb3u select value$ from props$ where name = 'GLOBAL_DB_NAME' DICTIONARY LOOKUP CURSOR CACHED 0000000A5D197FC8 171 SYS 0000000A58F72A80 2194907850 0ws7ahf1d78qa select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT( DICTIONARY LOOKUP CURSOR CACHED 0000000A5D197FC8 171 SYS 0000000A58EA14E8 3686391781 22ghqa7dvmrz5 SELECT trim(USERENV('SID')) global_name FROM DUAL OPEN Elapsed: 00:00:00.04 --sid171打开4个游标不关闭： 16:39:48 SYS@zkmdb(171)> variable x refcursor; 16:41:05 SYS@zkmdb(171)> variable z refcursor; 16:41:05 SYS@zkmdb(171)> variable b refcursor; 16:41:05 SYS@zkmdb(171)> variable a refcursor; 16:41:05 SYS@zkmdb(171)> exec open :x for select * from dual; PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 16:41:05 SYS@zkmdb(171)> exec open :z for select * from dual; PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 16:41:05 SYS@zkmdb(171)> exec open :b for select * from dual; PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 16:41:05 SYS@zkmdb(171)> exec open :a for select * from dual; PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 --sid321再次查看sid171信息： 16:41:40 SYS@zkmdb(321)> select * from v$open_cursor where sid=171; SADDR SID USER_NAME ADDRESS HASH_VALUE SQL_ID SQL_TEXT LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE ---------------- --- ---------- ---------------- ---------- --------------- ------------------------------------------------------------ ------------------- ----------- ----------------------------------- 0000000A5D197FC8 171 SYS 00000009C2371FF8 3991932091 9g6pyx7qz035v SELECT * FROM DUAL 16777235 OPEN 0000000A5D197FC8 171 SYS 00000009C2371FF8 3991932091 9g6pyx7qz035v SELECT * FROM DUAL 16777234 OPEN 0000000A5D197FC8 171 SYS 00000009C2371FF8 3991932091 9g6pyx7qz035v SELECT * FROM DUAL 16777233 OPEN 0000000A5D197FC8 171 SYS 00000009C2371FF8 3991932091 9g6pyx7qz035v SELECT * FROM DUAL 16777232 OPEN 0000000A5D197FC8 171 SYS 00000009C24530A0 2731315891 0zb36tajct4pm BEGIN open :z for select * from dual; END; DICTIONARY LOOKUP CURSOR CACHED 0000000A5D197FC8 171 SYS 00000009C22FEC88 4171414172 dgw83r3wa5fnw BEGIN open :a for select * from dual; END; OPEN 0000000A5D197FC8 171 SYS 00000009C15C9F60 1491910523 19z2ty5cftgvv BEGIN open :b for select * from dual; END; DICTIONARY LOOKUP CURSOR CACHED 0000000A5D197FC8 171 SYS 00000009C18CA5F8 1931145668 8vn7mxdtjpwf4 BEGIN open :x for select * from dual; END; DICTIONARY LOOKUP CURSOR CACHED 0000000A5D197FC8 171 SYS 0000000A5903FDC0 1950821498 459f3z9u4fb3u select value$ from props$ where name = 'GLOBAL_DB_NAME' DICTIONARY LOOKUP CURSOR CACHED 0000000A5D197FC8 171 SYS 0000000A58F72A80 2194907850 0ws7ahf1d78qa select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT( DICTIONARY LOOKUP CURSOR CACHED 0000000A5D197FC8 171 SYS 0000000A58EA14E8 3686391781 22ghqa7dvmrz5 SELECT trim(USERENV('SID')) global_name FROM DUAL DICTIONARY LOOKUP CURSOR CACHED 11 rows selected. Elapsed: 00:00:00.04 16:41:41 SYS@zkmdb(321)> select * from v$sesstat where sid=171 and statistic#=5; SID STATISTIC# VALUE ---------- ---------- ----- 171 5 5 Elapsed: 00:00:00.01
由此可见，v$open_cursor确实包含当前打开或者已缓存的游标信息。
ORA-01000 : Troubleshooting Open Cursors Issues (文档 ID 1477783.1)则更是重量级：
- Now one can see which all queries are causing maxing out of open cursors using below Sql:（谷歌翻译：现在可以使用以下 Sql 查看哪些所有查询导致打开的游标最大化：
）select sid ,sql_text, user_name, count(*) as "OPEN CURSORS" from v$open_cursor where sid in ($SID) group by sid ,sql_text, user_name;
count(*) as "OPEN CURSORS"..........
这缓存的游标也不能算是OPEN的游标啊..........