IBM Support

"ORA-01000: maximum open cursors exceeded" using DbConv to create new database

Troubleshooting


Problem

Administrator launches Controller Configuration. Administrator selects a new database, and attempts to populate it with tables via the Database Conversion Utility 'Run Steps' process. An error appears.

Symptom

OPEN_CURSORS = 300:
Creating new empty Database
Running DB Step: initial
Running script batch: script/oracle_createDB.batch
Running script: script/oracle/Sequences/SEQ_CRDCALCULATIONMETHODS_SORT.sql
<...>
Running script: script/oracle/Tables/XLANG.sql
Running script: script/oracle/Tables/XMOVEM.sql
** ERROR: java.sql.SQLException: ORA-01000: maximum open cursors exceeded
ORA-06512: at line 20

** ERROR: java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded

OPEN_CURSORS = 500:
Creating new empty Database
Running DB Step: initial
Running script batch: script/oracle_createDB.batch
<....>
Running script: script/oracle/Tables/XDB85.sql
Running script: script/oracle/Tables/XDB05.sql
** ERROR: java.sql.SQLException: ORA-01000: maximum open cursors exceeded
ORA-06512: at line 20

** ERROR: java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded

Cause

The Oracle server's 'OPEN_CURSORS' parameter is too small.

More Information
By default, the value for 'OPEN_CURSORS' (on a standard Oracle database server) is 300

  • This can be too small in some circumstances.

For more information on recommended Oracle settings, see separate IBM Technote #1346962.

Environment

The problem does not affect all Oracle environments.

Diagnosing The Problem

To check what the current value for '' is (for your Oracle database server), run the following script:

    select name, value from v$parameter where name like 'open_cursors';

Resolving The Problem

Increase the Oracle server's 'OPEN_CURSORS' parameter.

  • TIP: In one real-life example, the problem was solved by increasing the value from the default (300) to 2000.

Steps:
Ask your Oracle database administrator (DBA) to run the a script similar to the following:
    alter system set open_cursors = 2000 scope=both;

[{"Product":{"code":"SS9S6B","label":"Cognos Controller"},"Business Unit":{"code":"BU002","label":"Business Analytics"},"Component":"Controller","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.1.1","Edition":""}]

Document Information

Modified date:
15 June 2018

UID

swg21671446