FAQ: Error with Oracle: "ORA-01000: maximum open cursors exceeded"

When building a database with Oracle, I get an error, "ORA-01000: maximum open cursors exceeded." What can I do to fix this?

Short Answer

Increase open_cursors to 1000 in your Oracle server.

Long Answer

Though Sawmill does not directly use cursors, some ODBC drivers use several hundred cursors when Sawmill builds a database through them. This can cause an Oracle error if the maximum number of permitted cursors is insufficient.

You can monitor the number of open cursors by running this query against your Oracle database:

 SELECT v.value as numopencursors ,s.machine ,s.osuser,s.username FROM V$SESSTAT v, V$SESSION s WHERE v.statistic# = 3 and v.sid = s.sid;

To fix the problem increase the maximum number of cursors with this query:

  ALTER SYSTEM SET open_cursors = 1000 SCOPE=BOTH;

It is not necessary to restart the database server after running this command--it will affect the running instance immediately.