Oracle Notes

Sharing Oracle technology knowledge and experiences.

ORA-01000: Too many open cursors

Posted by sdefilip on July 25, 2009

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: