java.sql.SQLException: - ORA-01000: un maximum de curseurs ouverts dépassé

ORA-01000, le maximum-ouvrir-les curseurs de l'erreur, est un très commune erreur dans la base de données Oracle de développement. Dans le contexte de Java, il se produit lorsque l'application tente d'ouvrir plusieurs jeux de résultats qu'il y a configuré les curseurs sur une instance de base de données.

Causes les plus courantes sont les suivantes:

1. Erreur de Configuration de la

   • Vous avez plus de threads dans votre application d'interrogation de la base de données que les curseurs sur la DB. Dans un cas où vous avez une connexion et le pool de threads plus grand que le nombre de curseurs sur la base de données.
   • Vous avez beaucoup de développeurs ou les applications connectées à la même instance de base de données (qui sera probablement inclure de nombreux schémas) et, ensemble, vous utilisez trop grand nombre de connexions.

   • Solution:

     • L'augmentation du nombre de curseurs sur la base de données (si les ressources le permettent) ou
     • Diminuant le nombre de threads de l'application.

2. Curseur de fuite

   • Les applications n'est pas de clôture des jeux de résultats (JDBC) ou des curseurs (dans des procédures stockées dans la base de données)
   • Solution: le Curseur fuites sont des bugs; l'augmentation du nombre de curseurs sur la DB simplement retarde l'inévitable échec. Les fuites peuvent être trouvés en utilisant l'analyse statique de code, JDBC ou au niveau de l'application de la journalisation, et base de données de suivi.

Fond

Cette section décrit certains de la théorie derrière les curseurs et comment JDBC doit être utilisé. Si vous n'avez pas besoin de connaître l'arrière-plan, vous pouvez ignorer cela et aller directement à "Éliminer les Fuites".

Qu'est ce qu'un curseur?

Un curseur est une ressource sur la base de données que détient l'état d'une requête, plus précisément la position où un lecteur est dans un jeu de résultats. Chaque instruction SELECT a un curseur, et PL/SQL stockées procédures pouvez l'ouvrir et l'utiliser autant de curseurs comme ils le souhaitent. Vous pouvez en savoir plus sur les curseurs sur Orafaq.

Une instance de base de données sert généralement plusieurs schémas, de nombreux utilisateurs chacun avec plusieurs sessions. Pour ce faire, il dispose d'un nombre fixe de curseurs disponibles pour tous les schémas, les utilisateurs et les sessions. Lorsque tous les curseurs sont ouvertes (en utilisation) et de la demande qui nécessite un nouveau curseur, la demande échoue avec une erreur ORA-010000 erreur.

Recherche et définition du nombre de curseurs

Le nombre est normalement configuré par l'administrateur lors de l'installation. Le nombre de curseurs actuellement en cours d'utilisation, le nombre maximal et la configuration peut être consulté dans les fonctions de l'Administrateur en Oracle SQL Developer. À partir de SQL il peut être défini:

ALTER SYSTEM SET OPEN_CURSORS=1337 SID='*' SCOPE=BOTH;

Relatives JDBC dans la JVM pour les curseurs sur la DB

JDBC objets ci-dessous sont étroitement couplée à la suite de concepts de base de données:

• JDBC Connexion est le client de la représentation d'une base de données session et fournit une base de données transactions. Une connexion ne peut avoir qu'une seule opération ouverte à la fois (mais les transactions peuvent être imbriquées)
• JDBC ResultSet est pris en charge par un seul curseur sur la base de données. Lorsque la fonction close() est appelée sur le jeu de résultats, le curseur est libéré.
• JDBC CallableStatement appelle une procédure stockée sur la base de données, souvent écrites en PL/SQL. La procédure stockée peut créer de zéro, un ou plusieurs curseurs, et peut retourner un curseur comme JDBC ResultSet.

JDBC est thread-safe: Il est tout à fait OK pour passer les différents JDBC objets entre les threads.

Par exemple, vous pouvez créer la connexion à un fil, un autre thread peut utiliser cette connexion pour créer un PreparedStatement et un troisième fil peut traiter l'ensemble de résultats. La principale restriction est que vous ne pouvez pas avoir plus d'un jeu de résultats ouvrent sur un seul PreparedStatement à tout moment. Voir N'Oracle DB en charge plusieurs (parallèle) opérations par connexion?

Noter qu'une base de données de commettre se produit sur une Connexion, et donc tous les DML (INSERT, UPDATE et DELETE) sur que la connexion s'engager ensemble. Par conséquent, si vous souhaitez prendre en charge plusieurs opérations en même temps, vous devez avoir au moins une Connexion pour chaque Transaction simultanée.

De clôture JDBC objets

Un exemple typique de l'exécution d'un jeu de résultats est:

Statement stmt = conn.createStatement();
try {
    ResultSet rs = stmt.executeQuery( "SELECT FULL_NAME FROM EMP" );
    try {
        while ( rs.next() ) {
            System.out.println( "Name: " + rs.getString("FULL_NAME") );
        }
    } finally {
        try { rs.close(); } catch (Exception ignore) { }
    }
} finally {
    try { stmt.close(); } catch (Exception ignore) { }
}

Notez comment la clause finally ignore tout de l'exception soulevée par le close():

• Si vous fermez le jeu de résultats sans le try {} catch {}, il peut échouer et de prévenir l'Instruction en cours d'fermé
• Nous voulons permettre à toute exception soulevée dans le corps de l'essayer pour se propager à l'appelant.
  Si vous avez une boucle, par exemple, la création et l'exécution des Instructions, n'oubliez pas de fermer chaque Instruction dans la boucle.

Dans Java 7, Oracle a introduit la AutoCloseable interface qui remplace la plupart de la version 6 de Java standard avec quelques belles sucre syntaxique.

Holding JDBC objets

JDBC objets peuvent être conservés de façon sécuritaire dans des variables locales, instance de l'objet et les membres de la classe. Il est généralement préférable d':

• Utiliser l'instance de l'objet ou de la catégorie de membres à tenir JDBC objets qui sont réutilisés plusieurs fois sur une période plus longue, comme les Connexions et PreparedStatements
• Utiliser des variables locales pour les jeux de résultats, puisque ceux-ci sont obtenus, en boucle sur puis fermé généralement dans le cadre d'une seule fonction.

Il y a cependant une exception: Si vous utilisez des Ejb, ou un Servlet/JSP conteneur, vous devez suivre un strict modèle de thread:

• Seulement le Serveur d'Application crée des threads (avec laquelle il traite les demandes entrantes)
• Seulement le Serveur d'Application crée des connexions (que vous pouvez obtenir à partir d'un pool de connexion)
• Lors de l'enregistrement de valeurs (de l'état) entre les appels, vous devez être très prudent. Ne jamais stocker des valeurs dans votre propre caches ou des membres statiques - ce n'est pas sûr à travers les clusters et autres étranges conditions, et le Serveur d'Application peut faire des choses terribles à vos données. Au lieu d'utiliser les haricots stateful ou une base de données.
• En particulier, jamais tenir JDBC objets (Connexions, des jeux de résultats, PreparedStatements, etc) plus de distance différentes invocations - permettre à l'Application de Serveur de gérer cela. Le Serveur d'Application ne fournit pas seulement un pool de connexions, il met également en cache de votre PreparedStatements.

Des fuites

Il y a un certain nombre de processus et d'outils pour les aider à détecter et à éliminer JDBC fuites:

1. Au cours du développement - la capture des bugs au début est de loin la meilleure approche:

   1. Pratiques de développement: les Bonnes pratiques de développement devrait réduire le nombre de bugs dans votre logiciel avant qu'il ne quitte le développeur du bureau. Pratiques spécifiques comprennent:

      1. La programmation en binôme, à éduquer les personnes sans expérience suffisante
      2. Des revues de Code parce que beaucoup d'yeux valent mieux qu'une
      3. Les tests unitaires ce qui signifie que vous pouvez exercer à tout et à tous de votre base de code à partir d'un outil de test qui permet de reproduire les fuites trivial
      4. Utilisation les bibliothèques existantes pour le regroupement de connexions plutôt que de construire votre propre

   2. D'Analyse de Code statique: Utiliser un outil comme l'excellent Findbugs pour effectuer une analyse de code statique. Ce ramasse beaucoup d'endroits où l'close() n'a pas été correctement traitées. Findbugs a un plugin pour Eclipse, mais il fonctionne également en mode autonome pour un compromis, a intégrations Jenkins CI et d'autres outils de construction de

2. Au moment de l'exécution:

   1. Holdability et de s'engager

      1. Si le ResultSet holdability est ResultSet.CLOSE_CURSORS_OVER_COMMIT, puis le jeu de résultats est fermé lors de la Connexion.méthode commit() est appelée. Cela peut être défini à l'aide de la Connexion.setHoldability() ou en utilisant la surcharge de Connexion.createStatement() la méthode.

   2. Journalisation au moment de l'exécution.

      1. De mettre de bonnes instructions de journalisation dans votre code. Ceux-ci devraient être clairs et compréhensibles par le client, le personnel de soutien et vos coéquipiers peuvent comprendre, sans formation. Ils devraient être laconique et comprennent l'impression de l'état interne/valeurs des variables clés et les attributs de sorte que vous pouvez suivre la logique de traitement. Bon la journalisation est fondamentale pour le débogage d'applications, en particulier ceux qui ont été déployés.

      2. Vous pouvez ajouter un débogage pilote JDBC pour votre projet (pour le débogage - ne fait pas l'appliquer). Un exemple (je ne l'ai pas utilisé) est log4jdbc. Ensuite, vous devez faire une analyse simple sur ce fichier pour voir qui l'exécute ne correspond à proximité. Le comptage de l'ouvrir et ferme doit indiquer s'il existe un problème potentiel

         1. De surveillance de la base de données. Le moniteur de votre application en cours d'exécution à l'aide des outils tels que le SQL Developer " Moniteur SQL ou la fonction La quête du CRAPAUD. La surveillance est décrite dans cet article. Au cours de la surveillance, vous interrogez les curseurs ouverts (par exemple à partir de la table de v$sesstat) et de revoir leurs SQL. Si le nombre de curseurs est en augmentation, et (surtout) de devenir dominé par un identique instruction SQL, vous savez que vous avez une fuite avec SQL. Recherche de votre code et à l'examen.

D'autres pensées

Pouvez-vous utiliser WeakReferences à la poignée de fermeture des connexions?

Faible et mou références sont des moyens vous permettant de faire référence à un objet d'une manière qui permet à la JVM pour les ordures recueillir le point de référence à tout moment qu'il jugera bon de le faire (en supposant que il n'y a aucune référence forte chaînes à cet objet).

Si vous passez un ReferenceQueue dans le constructeur de la faiblesse ou de la faiblesse de la Référence, l'objet est placé dans le ReferenceQueue lorsque l'objet est GC ed lorsqu'il se produit (si elle a lieu). Avec cette approche, vous pouvez interagir avec l'objet de la finalisation et vous pourriez fermer ou finaliser l'objet à ce moment-là.

Fantôme références sont un peu plus étrange; leur seul but est de contrôler la finalisation, mais vous pouvez ne jamais obtenir une référence à l'objet d'origine, donc ça va être dur d'appeler la méthode close() sur elle.

Cependant, c'est rarement une bonne idée pour tenter de contrôle lors de la GC est exécuté (Faible, Doux et PhantomReferences vous laisser savoir après le fait que l'objet est mis en file d'attente pour le GC). En effet, si la quantité de mémoire de la JVM est grand (eg-Xmx2000m), vous pourriez jamais GC de l'objet, et vous aurez encore l'expérience de l'ORA-01000. Si la mémoire JVM est petite par rapport à votre programme, vous pouvez constater que le jeu de résultats et PreparedStatement objets sont GCed immédiatement après la création (avant de lire), ce qui ne sera vraisemblablement pas votre programme.

TL;DR: La faiblesse du mécanisme de référence n'est pas une bonne façon de gérer et de fermeture de l'Instruction et objets ResultSet.

Corriger votre Code comme ceci:

try
{ //method try starts  
  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();
  }
} //method/try ends
finally
{ //finally starts
   pStmt.close()
} 

Êtes-vous sûr que vous êtes vraiment à la fermeture de votre pStatements, les connexions et les résultats?

Pour analyser des objets ouverts, vous pouvez implment un délégataire modèle, qui encapsule le code autour de votre statemant, de connexion et d'objets de résultat. Donc, vous allez voir, si un objet a clôturé avec succès.

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

    class obj{ 

    public Connection getConnection(){
    return new ConnectionDelegator(...here create your connection object and put it into ...);

    } 
}


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() + " was closed");
        }
    }
}

Moi aussi j'avais été confronté à ce problème.Ci-dessous l'exception habitude de venir

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

J'ai été en utilisant Framework Spring avec Spring JDBC pour la couche dao.

Mon application qui a servi à la fuite des curseurs en quelque sorte, et au bout de quelques minutes, Il a utilisé pour me donner cette exception.

Après beaucoup de tests pour le débogage et l'analyse, j'ai trouvé qu'il y avait le problème avec le de l'Indexation, des Contraintes Primary Key et Unique dans l'un des Table être utilisé dans le Requête j'ai été en cours d'exécution.

Ma demande a été d'essayer de mettre à jour le Colonnes qui ont été à tort Indexé.
Donc, chaque fois que ma demande a été frapper à la requête de mise à jour sur les colonnes indexées, La base de données était en train de faire la réindexation basé sur les valeurs mises à jour. Il fuyait la curseurs.

J'ai été en mesure de résoudre le problème en faisant une Bonne Indexation sur les colonnes qui ont été utilisés pour la recherche dans la requête et à l'application de contraintes à chaque fois que nécessaire.

Avez-vous set autocommit=true? Si pas, essayez ceci:

{ //method try starts  
    String sql = "INSERT into TblName (col1, col2) VALUES(?, ?)";
    Connection conn = obj.getConnection()
    pStmt = conn.prepareStatement(sql);

    for (String language : additionalLangs) {
        pStmt.setLong(1, subscriberID);
        pStmt.setInt(2, Integer.parseInt(language));
        pStmt.execute();
        conn.commit();
    }
} //method/try ends { 
    //finally starts
    pStmt.close()
} //finally ends 

Ce problème survient principalement lorsque vous utilisez le regroupement de connexion, car lorsque vous fermez la connexion de connexion de revenir à la connexion de la piscine et de toutes les curseur associé à cette connexion ne jamais se ferment, comme la connexion à la base de données est toujours ouverte.
Donc, une autre solution est de diminuer le régime de ralenti du temps de connexion des connexions dans la piscine, donc peut-chaque fois que la connexion est inactive dans le cadre de, disons, 10 sec , la connexion à la base de données sera fermé et nouvelle connexion créée pour mettre dans la piscine.

Utilisant le traitement par lots entraînera une diminution des frais généraux. Voir le lien suivant pour exemples:
http://www.tutorialspoint.com/jdbc/jdbc-batch-processing.htm

J'ai eu ce problème avec ma source de données dans WildFly et Tomcat, de vous connecter à Oracle 10g.

J'ai trouvé que, sous certaines conditions, la déclaration n'était pas fermé, même lorsque la déclaration.close() a été invoquée.
Le problème était avec le Pilote Oracle, nous avons été à l'aide de: ojdbc7.jar. Ce pilote est prévu pour Oracle 12c et 11g, et il semble a quelques problèmes quand est utilisé avec Oracle 10g, donc je reviens à ojdbc5.jar et maintenant, tout fonctionne bien.