example - jdbc java descargar

java.sql.SQLException:-ORA-01000: se han superado los cursores máximos abiertos

Question

Obtengo una excepción SQL ORA-01000. Entonces tengo algunas consultas relacionadas con esto.

1. ¿Están los cursores abiertos máximos exactamente relacionados con el número de conexiones JDBC, o también están relacionados con los objetos de declaración y conjunto de resultados que hemos creado para una única conexión? (Estamos usando un grupo de conexiones)
2. ¿Hay alguna manera de configurar el número de objetos de declaración / conjunto de resultados en la base de datos (como las conexiones)?
3. ¿Es aconsejable utilizar la variable de instancia objeto de declaración / conjunto de resultados en lugar del método de declaración local / conjunto de resultados objeto en un único entorno de subprocesos?

4. ¿La ejecución de una declaración preparada en un bucle causa este problema? (Por supuesto, podría haber usado sqlBatch) Nota: pStmt está cerrado una vez que el ciclo ha terminado.

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

5. ¿Qué pasará si se invocan conn.createStatement () y conn.prepareStatement (sql) varias veces en un solo objeto de conexión?

Edit1: 6. ¿El uso del objeto de declaración de referencia Débil / Suave ayudará a prevenir la fuga?

Edit2: 1. ¿Hay alguna manera, puedo encontrar todos los "statement.close ()" faltantes en mi proyecto? Entiendo que no es una pérdida de memoria. Pero necesito encontrar una referencia de declaración (donde close () no se realiza) elegible para la recolección de basura? ¿Alguna herramienta disponible? ¿O tengo que analizarlo manualmente?

Por favor, ayúdame a entenderlo.

Solución

Para encontrar el cursor abierto en Oracle DB para el nombre de usuario -VELU

Vaya a la máquina ORALCE e inicie sqlplus como sysdba.

[oracle@db01 ~]$ sqlplus / as sysdba

Entonces corre

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 es posible, lea mi respuesta al final.

Answer 1

¿Estableciste autocommit = verdadero? Si no, prueba esto:

{ //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

Answer 2

Corrija su código de esta manera:

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()
}

¿Estás seguro de que estás cerrando tus declaraciones, conexiones y resultados?

Para analizar objetos abiertos, puede implementar un patrón delegador, que envuelve el código alrededor de sus objetos de estado, conexión y resultado. Entonces verás, si un objeto se cerrará exitosamente.

Un ejemplo para: 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");
}
}
}

Answer 3

El uso del procesamiento por lotes dará como resultado una menor sobrecarga. Vea el siguiente enlace para ejemplos: http://www.tutorialspoint.com/jdbc/jdbc-batch-processing.htm

Answer 4

En nuestro caso, estábamos usando Hibernate y teníamos muchas variables que hacen referencia a la misma entidad mapeada de Hibernate. Estábamos creando y guardando estas referencias en un bucle. Cada referencia abrió un cursor y lo mantuvo abierto.

Descubrimos esto mediante el uso de una consulta para verificar el número de cursores abiertos mientras ejecutamos nuestro código, avanzando con un depurador y comentando de manera selectiva las cosas.

En cuanto a por qué cada referencia nueva abría otro cursor, la entidad en cuestión tenía colecciones de otras entidades asignadas y creo que esto tenía algo que ver con eso (quizás no solo por sí solo, sino en combinación con la forma en que habíamos configurado el modo de búsqueda y ajustes de caché). Hibernate en sí ha tenido errores al no cerrar los cursores abiertos, aunque parece que se han corregido en versiones posteriores.

Como en realidad no necesitábamos tantas referencias duplicadas a la misma entidad, la solución fue dejar de crear y mantener todas esas referencias redundantes. Una vez que hicimos eso, el problema cuando estaba lejos.

Answer 5

Enfrenté el mismo problema porque estaba consultando DB por más de 1000 iteraciones. He usado try y finalmente en mi código. Pero todavía estaba recibiendo un error.

Para resolver esto, inicié sesión en Oracle db y corrí debajo de la consulta:

ALTERAR EL CONJUNTO DEL SISTEMA open_cursors = 8000 SCOPE = AMBOS;

Y esto resolvió mi problema de inmediato.

Answer 6

Este problema ocurre principalmente cuando usa la agrupación de conexiones porque cuando cierra la conexión esa conexión vuelve al grupo de conexiones y todos los cursores asociados con esa conexión nunca se cierran porque la conexión a la base de datos aún está abierta. Entonces, una alternativa es disminuir el tiempo de conexión inactiva de las conexiones en el grupo, así que cuando la conexión permanezca inactiva durante aproximadamente 10 segundos, la conexión con la base de datos se cerrará y se creará una nueva conexión para poner en el grupo.

Answer 7

Estoy agregando un poco más de comprensión.

1. Cursor solo se trata de un objeto de declaración; No es resultSet ni el objeto de conexión.
   
2. Pero aún tenemos que cerrar el conjunto de resultados para liberar algo de memoria del oráculo. Aún así, si no cierra el conjunto de resultados no se contará para CURSORES.
   
3. El objeto Statement de cierre también cerrará automáticamente el objeto resultset.
   
4. El cursor se creará para toda la instrucción SELECT / INSERT / UPDATE / DELETE.
   
5. Cada instancia de ORACLE DB se puede identificar utilizando Oracle SID; de manera similar, ORACLE DB puede identificar cada conexión utilizando SID de conexión. Ambos SID son diferentes.
   
6. Entonces, la sesión ORACLE no es más que una conexión jdbc (tcp); que no es más que un SID.
   
7. Si establecemos los cursores máximos como 500, entonces solo es para una sesión JDBC / conexión / SID.
8. Entonces podemos tener muchas conexiones JDBC con su respectivo no de cursores (declaraciones).
9. Una vez que finaliza la JVM, todas las conexiones / cursores se cerrarán, O JDBCConnection se cerrará CURSORS con respecto a esa conexión se cerrarán.
   
   

Loggin como sysdba.

En Putty (inicio de sesión de Oracle):

 [oracle@db01 ~]$ sqlplus / as sysdba

En SqlPlus:

Nombre de usuario: sys as sysdba

Establezca el valor de session_cached_cursors en 0 para que no tenga cursores cerrados.

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

Seleccionar el conjunto de validos OPEN_CURSORS existente por conexión en DB

 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;

A continuación se encuentra la consulta para encontrar la lista SID / conexiones con valores de cursor abiertos.

 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 = ''SCHEMA_NAME_IN_CAPS''

Utilice la consulta a continuación para identificar los sql en los cursores abiertos

 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 =''SCHEMA_NAME_IN_CAPS''

Ahora depura el código y disfruta !!! :)

Answer 8

Hoy enfrenté el mismo problema (ORA-01000). Tenía un bucle for en try {}, para ejecutar una instrucción SELECT en un DB de Oracle muchas veces, (cada vez cambiando un parámetro), y finalmente {} tenía mi código para cerrar Resultset, PreparedStatement y Connection como de costumbre . Pero tan pronto como alcancé una cantidad específica de bucles (1000) obtuve el error de Oracle sobre demasiados cursores abiertos.

Basado en la publicación de Andrew Alcock anterior, realicé cambios para que dentro del ciclo cerrase cada conjunto de resultados y cada extracto después de obtener los datos y antes de repetirlos, y eso resolvió el problema.

Adicionalmente, el mismo problema ocurrió en otro ciclo de Insertar declaraciones, en otro Oracle DB (ORA-01000), esta vez después de 300 declaraciones. De nuevo, se resolvió de la misma manera, por lo tanto, PreparedStatement o ResultSet o ambos, cuentan como cursores abiertos hasta que se cierran.

Answer 9

ORA-01000, el error de máxima apertura de cursores, es un error extremadamente común en el desarrollo de bases de datos Oracle. En el contexto de Java, ocurre cuando la aplicación intenta abrir más ResultSets que los cursores configurados en una instancia de base de datos.

Las causas comunes son:

1. Error de configuración

   • Tiene más hilos en su aplicación que consultan la base de datos que los cursores en la base de datos. Un caso es donde tiene una conexión y un grupo de subprocesos mayor que el número de cursores en la base de datos.
   • Tiene muchos desarrolladores o aplicaciones conectadas a la misma instancia de base de datos (que probablemente incluirá muchos esquemas) y, en conjunto, está utilizando demasiadas conexiones.

   • Solución:

     • Aumentar el número de cursores en la base de datos (si los recursos lo permiten) o
     • Disminuir el número de subprocesos en la aplicación.

2. Fuga de Cursor

   • Las aplicaciones no cierran ResultSets (en JDBC) o cursores (en procedimientos almacenados en la base de datos)
   • Solución : las fugas del cursor son errores; aumentar el número de cursores en el DB simplemente retrasa la falla inevitable. Las fugas se pueden encontrar usando análisis de código estático , JDBC o registro a nivel de aplicación y monitoreo de base de datos .

Fondo

Esta sección describe parte de la teoría detrás de los cursores y cómo debe usarse JDBC. Si no necesita conocer el fondo, puede omitir esto e ir directamente a ''Eliminar fugas''.

¿Qué es un cursor?

Un cursor es un recurso en la base de datos que contiene el estado de una consulta, específicamente la posición donde un lector se encuentra en un ResultSet. Cada instrucción SELECT tiene un cursor, y los procedimientos almacenados PL / SQL pueden abrirse y usar tantos cursores como necesiten. Puede obtener más información sobre los cursores en Orafaq .

Una instancia de base de datos normalmente sirve varios esquemas diferentes, muchos usuarios diferentes , cada uno con múltiples sesiones . Para hacer esto, tiene un número fijo de cursores disponibles para todos los esquemas, usuarios y sesiones. Cuando todos los cursores están abiertos (en uso) y entra la solicitud que requiere un nuevo cursor, la solicitud falla con un error ORA-010000.

Encontrar y establecer la cantidad de cursores

Normalmente, el DBA configura el número en la instalación. Se puede acceder al número de cursores actualmente en uso, al número máximo y a la configuración en las funciones de administrador en Oracle SQL Developer . Desde SQL se puede configurar con:

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

Relacionando JDBC en la JVM con los cursores en la base de datos

Los objetos JDBC a continuación están estrechamente relacionados con los siguientes conceptos de base de datos:

• JDBC Connection es la representación del cliente de una sesión de base de datos y proporciona transacciones de base de datos. Una conexión puede tener solo una transacción abierta en cualquier momento (pero las transacciones se pueden anidar)
• Un ResultSet JDBC es compatible con un solo cursor en la base de datos. Cuando se llama a close () en el ResultSet, se suelta el cursor.
• Un CallableStatement de JDBC invoca un procedimiento almacenado en la base de datos, a menudo escrito en PL / SQL. El procedimiento almacenado puede crear cero o más cursores y puede devolver un cursor como JDBC ResultSet.

JDBC es seguro para subprocesos: está bastante bien pasar los diversos objetos JDBC entre subprocesos.

Por ejemplo, puede crear la conexión en un hilo; otro hilo puede usar esta conexión para crear un PreparedStatement y un tercer hilo puede procesar el conjunto de resultados. La única restricción importante es que no puede tener más de un ResultSet abierto en un solo PreparedStatement en cualquier momento. Consulte ¿Oracle DB admite operaciones múltiples (paralelas) por conexión?

Tenga en cuenta que se produce una confirmación de la base de datos en una conexión, por lo que todos los DML (INSERTAR, ACTUALIZAR y ELIMINAR) en esa conexión se confirmarán conjuntamente. Por lo tanto, si desea admitir varias transacciones al mismo tiempo, debe tener al menos una conexión para cada transacción simultánea.

Cierre de objetos JDBC

Un ejemplo típico de ejecución de un ResultSet es:

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) { }
}

Observe cómo la cláusula finally ignora cualquier excepción planteada por close ():

• Si simplemente cierra ResultSet sin try {} catch {}, podría fallar e impedir que se cierre el Statement
• Queremos permitir que cualquier excepción planteada en el cuerpo del intento se propague a la persona que llama. Si tiene un bucle, por ejemplo, crear y ejecutar declaraciones, recuerde cerrar cada instrucción dentro del bucle.

En Java 7, Oracle introdujo la interfaz AutoCloseable que reemplaza la mayor parte de la repetición de Java 6 con un buen azúcar sintáctico.

Manteniendo objetos JDBC

Los objetos JDBC pueden mantenerse de forma segura en variables locales, instancia de objeto y miembros de clase. En general, es una mejor práctica para:

• Utilice la instancia de objeto o los miembros de la clase para contener objetos JDBC que se reutilizan varias veces durante un período más largo, como las conexiones y las declaraciones preparadas.
• Use las variables locales para los ResultSets, ya que estos se obtienen, se enrutan y luego se cierran típicamente dentro del alcance de una sola función.

Sin embargo, hay una excepción: si está utilizando EJB o un contenedor Servlet / JSP, debe seguir un modelo de subprocesamiento estricto:

• Solo el servidor de aplicaciones crea subprocesos (con los que maneja las solicitudes entrantes)
• Solo el servidor de aplicaciones crea conexiones (que obtiene del grupo de conexiones)
• Al guardar valores (estado) entre llamadas, debe tener mucho cuidado. Nunca almacene valores en sus propios cachés o miembros estáticos; esto no es seguro entre clusters y otras condiciones extrañas, y Application Server puede hacer cosas terribles con sus datos. En su lugar, use beans con estado o una base de datos.
• En particular, nunca mantenga los objetos JDBC (Connections, ResultSets, PreparedStatements, etc.) sobre diferentes invocaciones remotas: permita que el Servidor de Aplicaciones administre esto. El servidor de aplicaciones no solo proporciona un grupo de conexiones, sino que también almacena en caché sus declaraciones preparadas.

Eliminando fugas

Hay una cantidad de procesos y herramientas disponibles para ayudar a detectar y eliminar fugas de JDBC:

1. Durante el desarrollo, detectar los errores al principio es, de lejos, el mejor enfoque:

   1. Prácticas de desarrollo: las buenas prácticas de desarrollo deben reducir la cantidad de errores en su software antes de que salga del escritorio del desarrollador. Las prácticas específicas incluyen:

      1. Programación de par , para educar a aquellos sin suficiente experiencia
      2. Revisiones de código porque muchos ojos son mejores que uno
      3. Pruebas unitarias, lo que significa que puede ejercitar toda su base de código desde una herramienta de prueba que hace que las fugas de reproducción sean triviales
      4. Utilice las bibliotecas existentes para la agrupación de conexiones en lugar de construir su propio

   2. Análisis de código estático: utilice una herramienta como los excelentes Findbugs para realizar un análisis de código estático. Esto recoge muchos lugares donde el close () no se ha manejado correctamente. Findbugs tiene un plugin para Eclipse, pero también se ejecuta de manera independiente para las one-offs, tiene integraciones en Jenkins CI y otras herramientas de compilación

2. En tiempo de ejecución:

   1. Holdability y commit

      1. Si la capacidad de mantenimiento de ResultSet es ResultSet.CLOSE_CURSORS_OVER_COMMIT, el ResultSet se cierra cuando se llama al método Connection.commit (). Esto puede establecerse usando Connection.setHoldability () o utilizando el método Connection.createStatement () sobrecargado.

   2. Logging en tiempo de ejecución.

      1. Ponga buenas declaraciones de registro en su código. Estos deben ser claros y comprensibles para que el cliente, el personal de soporte y los compañeros de equipo puedan entenderlos sin capacitación. Deben ser concisos e incluir la impresión de los valores de estado / internos de las variables clave y los atributos para que pueda rastrear la lógica de procesamiento. Un buen registro es fundamental para las aplicaciones de depuración, especialmente las que se han implementado.

      2. Puede agregar un controlador JDBC de depuración a su proyecto (para la depuración, no lo implemente). Un ejemplo (no lo he usado) es JDBC . Luego debe hacer un análisis simple en este archivo para ver qué ejecuciones no tienen un cierre correspondiente. Contando el abrir y cerrar debe resaltar si hay un problema potencial

         3. Monitoreo de la base de datos Controle su aplicación en ejecución usando herramientas como SQL Developer ''Monitor SQL'' o Quest''s TOAD . El monitoreo se describe en este artículo . Durante la supervisión, consulta los cursores abiertos (por ejemplo, de la tabla v $ sesstat) y revisa su SQL. Si el número de cursores está aumentando y (lo que es más importante) está dominado por una declaración SQL idéntica, sabrá que tiene una pérdida con ese SQL. Busque su código y revise.

Otros pensamientos

¿Puedes usar WeakReferences para manejar las conexiones de cierre?

Las referencias débiles y suaves son formas de permitirle hacer referencia a un objeto de manera que permita a la JVM recoger el referente en la basura en cualquier momento que lo considere apropiado (suponiendo que no haya cadenas de referencia fuertes para ese objeto).

Si pasa una ReferenceQueue en el constructor a la referencia débil o débil, el objeto se coloca en la ReferenceQueue cuando el objeto recibe GC cuando ocurre (si es que ocurre). Con este enfoque, puede interactuar con la finalización del objeto y puede cerrar o finalizar el objeto en ese momento.

Las referencias ficticias son un poco más extrañas; su propósito es solo controlar la finalización, pero nunca se puede obtener una referencia al objeto original, por lo que será difícil llamar al método close ().

Sin embargo, rara vez es una buena idea intentar controlar cuándo se ejecuta el GC (Weak, Soft y PhantomReferences le informan después de que el objeto está en cola para GC). De hecho, si la cantidad de memoria en la JVM es grande (p. Ej., -Xmx2000m), es posible que nunca haga GC en el objeto, y aún experimentará el ORA-01000. Si la memoria JVM es pequeña en relación con los requisitos de su programa, puede encontrar que los objetos ResultSet y PreparedStatement se GCed inmediatamente después de la creación (antes de que pueda leer de ellos), lo que probablemente falle en su programa.

TL; DR: el mecanismo de referencia débil no es una buena forma de administrar y cerrar objetos Statement y ResultSet.

Answer 10

Si su aplicación es una aplicación Java EE que se ejecuta en Oracle WebLogic como servidor de aplicaciones, una posible causa de este problema es la configuración del Tamaño de la memoria caché de Statement en WebLogic.

Si la configuración del tamaño de caché de estado para una fuente de datos particular es igual o mayor que la configuración de recuento máximo de cursor de la base de datos Oracle, todos los cursores abiertos pueden ser consumidos por las declaraciones SQL en caché que WebLogic mantiene abiertas, lo que resulta en el error ORA-01000.

Para solucionar esto, reduzca la configuración del Tamaño de la memoria caché de declaración para cada fuente de datos de WebLogic que apunta a la base de datos de Oracle para que sea significativamente menor que la configuración de recuento máximo de cursor en la base de datos.

En la Consola de administración de WebLogic 10, la configuración del Tamaño de la memoria caché de Statement para cada fuente de datos se puede encontrar en Servicios (navegación izquierda)> Fuentes de datos> (fuente de datos individuales)> pestaña Pool de conexiones.

Answer 11

Tuve este problema con mi fuente de datos en WildFly y Tomcat, conectándome a un Oracle 10g.

Descubrí que bajo ciertas condiciones, la declaración no se cerró incluso cuando se invocó a statement.close (). El problema era con el controlador de Oracle que estábamos usando: ojdbc7.jar. Este controlador está destinado para Oracle 12c y 11g, y parece que tiene algunos problemas cuando se usa con Oracle 10g, por lo que bajó a ojdbc5.jar y ahora todo funciona correctamente.

Answer 12

Yo también me había enfrentado a este problema. La siguiente excepción solía venir

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

Estaba usando Spring Framework con Spring JDBC para la capa dao.

Mi aplicación solía fugar cursores de alguna manera y después de unos minutos más o menos, Solía ​​darme esta excepción.

Después de una gran cantidad de depuración y análisis exhaustivos, descubrí que existía el problema con la indexación, la clave principal y las restricciones únicas en una de las tablas que se utilizaban en la consulta que estaba ejecutando.

Mi aplicación intentaba actualizar las Columnas que se indexaron por error. Entonces, cada vez que mi aplicación tocaba la consulta de actualización en las columnas indexadas, la base de datos intentaba reindexar en función de los valores actualizados. Estaba filtrando los cursores .

Pude resolver el problema haciendo una correcta indexación en las columnas que se usaron para buscar en la consulta y aplicar las restricciones apropiadas donde sea necesario.

Answer 13

consulta para encontrar sql que se abrió.

SELECT s.machine, oc.user_name, oc.sql_text, count(1)
FROM v$open_cursor oc, v$session s
WHERE oc.sid = s.sid
and S.USERNAME=''XXXX''
GROUP BY user_name, sql_text, machine
HAVING COUNT(1) > 2
ORDER BY count(1) DESC