Thursday, October 10, 2019

ORA-01000: maximum open cursors exceeded


Cursor is a pointer to a memory area which is opened to execute an SQL statement issued by the end user/application. It means that every SQL (SELECT/DML) you want to execute would require utilizing this memory area to execute the SQL.

OPEN_CURSORS
To limit the memory used by a session to open cursors for the execution of SQLs, the parameter OPEN_CURSORS is used. This parameter sets the limits of simultaneously opened cursors for each session. No session can open more cursors than specified in this parameter.  I have checked different websites/blogs and have found out that experts suggest setting its value to around 1000 and this should be enough for most of the applications. If you still see high number of cursors being opened by sessions (you should have alerts set for open cursor matrix in the OEM or in any other monitoring tool that you use) or if you see ORA-01000, it means that your application needs the value of OPEN_CURSORS to be increased. You can dynamically change value of this parameter.
Increasing the value of this parameter and keep increasing might not be very good, so you should involve application team to confirm if cursors being opening by the code are also closed timely after work done by the cursor completes.
After you set this parameter to a sufficiently high value, you should not see sessions failing with ORA-01000 error message.
Following is a query that can show you current open cursors utilization by each session.
select a.username,a.sid,a.program, b.value "opened_cursors" from v$session a, v$sesstat b, v$statname c where a.sid=b.sid and  b.statistic#=c.statistic#  and c.name = 'opened cursors current' and a.username is not null order by b.value;

USERNAME                   SID     PROGRAM                                                       opened_cursors
------------------------------ ---------- ---------------------------------------------------------------- --------------
C##SALMAN                                   129   sqlplus.exe                                                           12
C##SALMAN                                    19     sqlplus.exe                                                           3

No comments:

Post a Comment

Popular Posts - All Times