Java.sql.SQLException: - ORA-01000: nombre maximum de curseurs ouverts dépassé

Question

Je reçois une exception SQL ORA-01000. J'ai donc quelques requêtes en rapport avec cela.

1. Les curseurs ouverts maximum sont-ils directement liés au nombre de connexions JDBC, ou sont-ils aussi liés aux objets de déclaration et de résultat que nous avons créés pour une seule connexion ? (Nous utilisons un pool de connexions)

2. Existe-t-il un moyen de configurer le nombre d'objets de déclaration/résultat dans la base de données (comme les connexions) ?

3. Est-il conseillé d'utiliser des objets de déclaration/résultat variables d'instance au lieu d'objets de déclaration/résultat locaux à la méthode dans un environnement à un seul thread ?

4. Est-ce que l'exécution d'une instruction préparée dans une boucle cause ce problème ? (Bien sûr, j'aurais pu utiliser sqlBatch) Note : pStmt est fermé une fois la boucle terminée.

   { //début du try de la méthode  
     String sql = "INSERT into TblName (col1, col2) VALUES(?, ?)";
     pStmt = obj.getConnection().prepareStatement(sql);
     pStmt.setLong(1, subscriberID);
     for (String language : additionalLangs) {
       pStmt.setInt(2, Integer.parseInt(language));
       pStmt.execute();
     }
   } //fin de la méthode/du try
   
   { //début du finally
      pStmt.close()
   } //fin du finally 

5. Que se passera-t-il si conn.createStatement() et conn.prepareStatement(sql) sont appelés plusieurs fois sur un seul objet connection ?

Édition 1: 6. Est-ce que l'utilisation d'un objet de déclaration à référence faible/forte aidera à prévenir les fuites de mémoire ?

Édition 2: 1. Y a-t-il un moyen de trouver tous les "statement.close()" manquants dans mon projet ? Je comprends que ce n'est pas une fuite de mémoire. Mais j'ai besoin de trouver une référence à une déclaration (où close() n'a pas été effectué) éligible à la collecte d'ordures ? Un outil disponible ? Ou dois-je l'analyser manuellement ?

Veuillez m'aider à comprendre cela.

Solution

Pour trouver le curseur ouvert dans la base de données Oracle pour le nom d'utilisateur - VELU

Allez sur la machine ORACLE et lancez sqlplus en tant que sysdba.

[oracle@db01 ~]$ sqlplus / as sysdba 

Ensuite, exécutez

SELECT   A.VALUE,
    S.USERNAME,
    S.SID,
    S.SERIAL#
  FROM V$SESSTAT A,
    V$STATNAME B,
    V$SESSION S
  WHERE A.STATISTIC# = B.STATISTIC#
    AND S.SID        = A.SID
    AND B.NAME       = 'opened cursors current'
    AND USERNAME     = 'VELU';

Si possible, veuillez lire ma réponse pour mieux comprendre ma solution

Answer 1

Je suis en train d'ajouter quelques compréhensions supplémentaires.

1. Le curseur concerne uniquement un objet statement; ce n'est ni un resultSet ni l'objet de connexion.

2. Mais nous devons quand même fermer le resultSet pour libérer de la mémoire Oracle. Même si vous ne fermez pas le resultSet, cela ne sera pas compté pour les CURSORS.

3. Fermer l'objet Statement fermera automatiquement l'objet resultSet.

4. Un curseur sera créé pour toutes les déclarations SELECT/INSERT/UPDATE/DELETE.

5. Chaque instance de la base de données ORACLE peut être identifiée en utilisant l'oracle SID; de même, la base de données ORACLE peut identifier chaque connexion en utilisant la connexion SID. Les deux SID sont différents.

6. Ainsi, une session ORACLE n'est rien d'autre qu'une connexion jdbc(tcp); qui n'est rien d'autre qu'un SID.

7. Si nous définissons un maximum de 500 curseurs, cela concerne uniquement une seule session/connexion/SID JDBC.

8. Nous pouvons donc avoir de nombreuses connexions JDBC avec leur nombre respectif de curseurs (déclarations).

9. Une fois que le JVM est terminé, toutes les connexions/curseurs seront fermés, OU si JDBCConnection est fermé, les CURSORS par rapport à cette connexion seront fermés.

Connexion en tant que sysdba.

Dans Putty (Connexion Oracle):

  [oracle@db01 ~]$ sqlplus / en tant que sysdba

Dans SqlPlus:

Nom d'utilisateur: sys en tant que sysdba

Définir la valeur de session_cached_cursors à 0 afin de ne pas fermer les curseurs.

 alter session set session_cached_cursors=0
 select * from V$PARAMETER where name='session_cached_cursors'

Sélectionnez les valeurs OPEN_CURSORS existantes définies par connexion dans la base de données

 SELECT max(a.value) as highest_open_cur, p.value as max_open_cur FROM v$sesstat a, v$statname b, v$parameter p WHERE a.statistic# = b.statistic# AND b.name = 'opened cursors current' AND p.name= 'open_cursors'  GROUP BY p.value;

Voici la requête pour trouver la liste des SID/connexions avec les valeurs de curseurs ouverts.

 SELECT a.value, s.username, s.sid, s.serial#
 FROM v$sesstat a, v$statname b, v$session s
 WHERE a.statistic# = b.statistic#  AND s.sid=a.sid 
 AND b.name = 'opened cursors current' AND username = 'NOM_DU_SCHEMA_EN_MAJUSCULE'

Utilisez la requête ci-dessous pour identifier les SQL dans les curseurs ouverts

 SELECT oc.sql_text, s.sid 
 FROM v$open_cursor oc, v$session s
 WHERE OC.sid = S.sid
 AND s.sid=1604
 AND OC.USER_NAME ='NOM_DU_SCHEMA_EN_MAJUSCULE'

Maintenant déboguez le code et profitez!!! :)

Answer 2

Corrigez votre code comme ceci :

try
{ //début du bloc try
  String sql = "INSERT into TblName (col1, col2) VALUES(?, ?)";
  pStmt = obj.getConnection().prepareStatement(sql);
  pStmt.setLong(1, subscriberID);
  for (String language : additionalLangs) {
    pStmt.setInt(2, Integer.parseInt(language));
    pStmt.execute();
  }
} //fin du bloc try
finally
{ //début du bloc finally
   pStmt.close()
} 

Êtes-vous sûr de bien fermer vos pStatements, connexions et résultats ?

Pour analyser les objets ouverts, vous pouvez implémenter un modèle délégateur, qui enveloppe du code autour de vos objets statement, connection et result. Ainsi, vous pourrez voir si un objet sera fermé avec succès.

Un exemple pour : pStmt = obj.getConnection().prepareStatement(sql);

    class obj{ 

    public Connection getConnection(){
    return new ConnectionDelegator(...ici créez votre objet de connexion et mettez-le dans ...);

    } 
}

class ConnectionDelegator implements Connection{
    Connection delegates;

    public ConnectionDelegator(Connection con){
       this.delegates = con;
    }

    public Statement prepareStatement(String sql){
        return delegates.prepareStatement(sql);
    }

    public void close(){
        try{
           delegates.close();
        }finally{
           log.debug(delegates.toString() + " a été fermé");
        }
    }
}

Answer 3

J'avais moi aussi rencontré ce problème. L'exception ci-dessous s'affichait :

java.sql.SQLException: - ORA-01000: maximum open cursors exceeded

J'utilisais le Framework Spring avec Spring JDBC pour la couche dao.

Mon application avait tendance à laisser fuiter des curseurs et après quelques minutes, elle me donnait cette exception.

Après beaucoup de débogage approfondi et d'analyse, j'ai découvert que le problème se situait au niveau de l'Indexation, des Clés Primaire et des Contraintes Uniques dans l'une des Tables utilisées dans la Requête que j'exécutais.

Mon application essayait de mettre à jour des Colonnes qui étaient accidentellement Indexées. Ainsi, chaque fois que mon application exécutait la requête de mise à jour sur les colonnes indexées, la base de données essayait de re-indexer en fonction des valeurs mises à jour. Cela provoquait des fuites de curseurs.

J'ai résolu le problème en effectuant une indexation correcte sur les colonnes utilisées dans la recherche de la requête et en appliquant les contraintes appropriées lorsque c'était nécessaire.

Answer 4

Si votre application est une application Java EE fonctionnant sur Oracle WebLogic en tant que serveur d'application, une cause possible de ce problème est le paramétrage de la Taille du cache de déclaration dans WebLogic.

Si le paramètre Taille du cache de déclarations pour une source de données particulière est à peu près égal, voire supérieur, au paramètre du nombre maximum de curseurs ouverts de la base de données Oracle, tous les curseurs ouverts peuvent être consommés par les instructions SQL mises en cache qui sont maintenues ouvertes par WebLogic, ce qui entraîne l'erreur ORA-01000.

Pour remédier à cela, réduisez le paramètre Taille du cache de déclarations pour chaque source de données WebLogic qui pointe vers la base de données Oracle pour qu'il soit significativement inférieur au paramètre du nombre maximum de curseurs sur la base de données.

Dans la console d'administration WebLogic 10, le paramètre Taille du cache de déclarations pour chaque source de données se trouve dans Services (menu de navigation de gauche) > Sources de données > (source de données individuelle) > Onglet Pool de connexions.

Answer 5

J'ai rencontré le même problème (ORA-01000) aujourd'hui. J'avais une boucle for dans le try{}, pour exécuter une instruction SELECT dans une base de données Oracle plusieurs fois, (à chaque fois en changeant un paramètre), et en finally{} j'avais mon code pour fermer Resultset, PreparedStatement et Connection comme d'habitude. Mais dès que j'ai atteint un nombre spécifique de boucles (1000), j'ai eu l'erreur Oracle sur trop de curseurs ouverts.

En me basant sur le message d'Andrew Alcock ci-dessus, j'ai apporté des modifications pour que à l'intérieur de la boucle, je ferme chaque resultset et chaque statement après avoir obtenu les données et avant de boucler à nouveau, et cela a résolu le problème.

De plus, le même problème exact s'est produit dans une autre boucle d'Instructions INSERT, dans une autre base de données Oracle (ORA-01000), cette fois après 300 instructions. Encore une fois, il a été résolu de la même manière, donc soit le PreparedStatement soit le ResultSet ou les deux, comptent comme des curseurs ouverts jusqu'à ce qu'ils soient fermés.