ORA-01000: maximum open cursors exceeded

The Problem

You are using the NID command to change the ID of your DB and you have issued the following command:

D:\oracle\product\11.2.0\dbhome_11\bin\nid target=sys/password dbname=TEST setname=YES

You then get the following errors message:

NID-00111: Oracle error reported from target database while executing
dbms_backup_restore.nidprocessdf(:fno, :istemp, :skipped, :idchged, :nmchged);
ORA-01000: maximum open cursors exceeded

The Cause

As the error points out, you have exceeded the maximum number of cursors which can be opened at any one time and this is the cause of the ORA-01000 and NID-00111 error messages.

The Solution

First of all, you should ensure that you revert the previous attempt at changing the DB ID so that the DB is put back into a consistent state and you are able to try again. To revert the DB ID failed attempt use the following command:

D:\oracle\product\11.2.0\dbhome_11202\bin\nid target=sys/password revert=Y
Your next action is to increase the value for the open_curors parameter so that the initial command can be run successfully. Check what value you have currently set using the show command:

SHOW PARAMETER open_cursors

Then increase it to an appropriate value using an alter system command:

ALTER SYSTEM SET open_cursors = xxxxx SCOPE=SPFILE;

Then run the above command again making sure that you have restarted the DB and it is mounted and not open.

You may find that you are hitting another error message now, the NID-00111 error message, coupled with the ORA-01116 error. If you are hitting this issue, you can read my other article NID-00111: Oracle error reported from target database.

Like it, share it...

Category: Database Config

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *