IBM Support

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

Technote (troubleshooting)


Problem(Abstract)

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;

Related information

1340950 - How to increase the amount of Oracle cursors
1346962 - Best Practices (Performance Tuning) for Oracl

Document information

More support for: Cognos Controller
Controller

Software version: 10.1.1

Operating system(s): Windows

Reference #: 1671446

Modified date: 23 June 2017


Translate this page: