Saturday, July 25, 2009

ORA-01000: Too many open cursors

You are running a long process, such as an export/import or SQL*Loader script, and get the "ORA-01000: Too many open cursors" error from your Oracle database.

This problem could be encountered because Oracle can keep closed cursors in cache which are included in the total open cursors count. One way to determine how many cursors are actually open in a database is to submit the following SQL:

"select value, name from v$sysstat where statistic# in (2,3);"

The standard recommended solution to the ORA-01000 is to increase the OPEN_CURSORS database initialization parameter. However, there is a workaround:

Break down your process into smaller pieces. An example is, instead of issuing a request for a full database export, do your export on a schema or table basis. You will have the inconvenience of having to execute more than one script or command, however, you will get around this error without having to change database initialization parameters and doing a shutdown/restart of your database.

No comments:

Post a Comment