ORA-01000: maximum open cursors exceeded – It’s typical error encountered in Oracle database. It happens when too much cursors are opened on an oracle instance.

Maximum number of opened cursors for an instance is limited by parameter

open_cursors

usually for small databases it’s about 300 and can be very huge for bigger ones. To increase this parameter it requires to bounce database instance.

To find out who opened many cursors on instance use following query

SELECT SID, count(*) ses_cursors, sum(count(*)) OVER() total_cursors
FROM v$open_cursor
GROUP BY SID
ORDER BY 2 DESC;

       SID ses_cursors total_cursors 
---------- ----------- -------------
       572         115           837 
      2835          95           837 
...
      1987           1           837 
      1417           1           837 

 41 rows selected

then you can investigate a given session why so many cursors are opened.

Have a fun 🙂

Tomasz