ORA-01000: maximum open cursors exceeded

Problem:

You are continuously getting ORA-01000 and cannot even create a table.

Solution:

Usually, this error is encountered when init.ora parameter OPEN_CURSORS is set too low.

Or developers write code that does not close cursors properly.

Determine the current setting of OPEN_CURSORS parameter:

--If the value is less than 300, consider setting it higher SQL> show parameter open_cursors; --You can dynamically set this parameter SQL> alter system set open_cursors=1000 scope=both;

After modifying OPEN_CURSORS to a higher value and the application continues to exceed the maximum value, you probably have an issue with code that is not properly closing cursors.

If you work in an environment that has thousands of connections to the database, you may want to view only the top cursor-consuming sessions.

Use this query to get the top 20 consumers:

select * from ( select a.value, c.username, c.machine, c.sid, c.serial# from v$sesstat a ,v$statname b, v$session c where a.statistic# = b.statistic# and c.sid = a.sid and b.name = 'opened cursors current' and a.value != 0 and c.username IS NOT NULL order by 1 desc,2) where rownum < 21;

I hope this helps.

Please feel free to leave your questions or suggest improvements to this section.