How to resolve the error: ORA-01000: maximum open cursors exceeded in TIBCO ActiveMatrix BusinessWorks Plug-in for Database

How to resolve the error: ORA-01000: maximum open cursors exceeded in TIBCO ActiveMatrix BusinessWorks Plug-in for Database

book

Article ID: KB0073792

calendar_today

Updated On:

Products

TIBCO ActiveMatrix BusinessWorks Plug-in for Database

Description

In TIBCO ActiveMatrix BusinessWorks Plug-in for Database 7.2(Adapter), when the huge amount of records are inserted in the database then the "ORA-01000: maximum open cursors exceeded " error can occur.

In TIBCO ActiveMatrix BusinessWorks Plug-in for Database 8.x(Plugin), when the huge amount of records are in the processing by the DataEventPoller activity, the error "ORA-01000: maximum open cursors exceeded " can be occurred intermittently. 

Other causes:

The "ORA-01000: maximum open cursors exceeded " is an extremely common error in Oracle database development. It happens when the ADB application attempts to open more ResultSets than there are configured cursors on a database instance.

When multiple threads in ADB application querying the database than cursors on the database. One case is where connection and thread pool configured larger than the number of cursors on the database.

When multiple applications connected to the same database instance (which will probably include many schemas) and together they are using too many connections.
 

Issue/Introduction

Set thread count in BW Engine less than open-cursors in the database.

Environment

================== Product: TIBCO ActiveMatrix BusinessWorks Plug-in for Database Version: 7.2,8.x OS: All Supported Operating Systems ==================

Resolution

Solution:

1. Increasing the number of cursors on the database.
     In the oracle, the "init.ora" file the parameter "open_cursors" defines the maximum of opened cursors a session can have at once. 
     It has a default value of 50. If the application exceeds this number the error "ORA-01000: maximum open cursors exceeded" is raised.

     Set "open-cursors" value as per the required application performance. 

2. Decreasing the number of threads in the application.
     This is the another way to avoid the error "ORA-01000: maximum open cursors exceeded" should not occur.
     Set thread count in the config.ini under "<BWHOME>\bw\<version>\domains\<domainname>\appnodes\<AppspaceName>\<AppnodeName>"

     bw.engine.threadCount=<INT value>

     BW Engine Thread Count.  This is an optional property and it specifies the BW Engine thread pool size.  
     The default value is "8" and the supported value for this property is an integer which must be greater than zero.  

   From above, to avoid this issue set thread count in BW Engine less than open-cursors in the database.