Max open cursors exceeded - Oracle 11g | ibatis 2.3.4 | spring 3.0.3

1
votes

We have recently migrated from Sybase to Oracle 11g database. Our application uses spring 3.0.3 along with IBATIS 2.3.4 to interact with database. Recently, we have started facing ORA-01000 Maximum open cursors exceeded exception. We talked to our DBA and he confirmed that for each row insertion we are occupying one cursor which is not correct. The cursor size is 300 as set by the DBA.

Can you please suggest how do we handle this through ibatis configuration or Java code? We've never faced this issue with Sybase.

The current code structure in our DAO class:

try{
    sqlMapClient.startTransaction();
    sqlMapClient.startBatch();

    for(...){
        sqlMapClient.insert(<in table 1>);
        sqlMapClient.insert(<in table 2>);
    }

    sqlMapClient.executeBatchDetailed();
    sqlMapClient.commitTransaction();
}

catch(..){

}

finally{

    sqlMapClient.endTransaction();

}

Note: We cannot perform intermediate commit as the operation needs to be all or none.

1
oracleibatis

1 Answers

0
votes

You can increase cursor number using with following statement. Only DBA user can execute the following statement. This problem will occur whenever if you have performance issue with your pl/sql coding. Please try to tune procedure / sql.

ALTER SYSTEM SET open_cursors = 400 SCOPE=BOTH;