#查看用户cursor的使用情况
col sid for a9999999999
col osuser for a20
col machine for a20
col num_curs for a9999999999
select o.sid,osuser,machine,count(*) num_curs
from v$open_cursor o , v$session s
where username ='&username'
and o.sid=s.sid
group by o.sid ,osuser,machine
ordre by num_curs desc ;Troubleshooting :
1. Check the value of the OPEN_CURSORS parameter
2. A DBA can use the V$OPEN_CURSOR view to see what cursors are in use at any point in time in each session
3. If it is proving difficult to identify the reason for the ORA-1000 error then it is possible to get the user session to generate a trace file when the error occurs by setting the following event either in affected sessions or systemwide:
For the current session:
ALTER SESSION SET EVENTS '1000 trace name errorstack level 3';
--OR--
Systemwide:
ALTER SYSTEM SET EVENTS '1000 trace name errorstack level 3';
This event will cause a trace file to be written by a session the first time it hits an ORA-1000. Provided MAX_DUMP_FILE_SIZE is large enough this trace should help identify what cursors in the session are being used for and hence help identify the cause of the ORA-1000.
(See Note:75713.1 for important information about setting events)
Warning :
Although ORA-1000 can often be tackled by increasing OPEN_CURSORS it is not sensible to just keep increasing this value as open cursors hold resources. Eg: If you have increased OPEN_CURSORS up to several hundred then it may be better to identify what all the cursors are for rather than just blindly increasing OPEN_CURSORS further.
alter system set event '1000 trace name errorstack level 3' ;
