java.sql.SQLException: – ORA-01000: cursores abertos máximos excedidos

Estou recebendo uma exceção SQL ORA-01000. Então eu tenho algumas dúvidas relacionadas a isso.

  1. O máximo de cursores abertos está exatamente relacionado ao número de conexões JDBC ou eles também estão relacionados aos objects de declaração e de conjunto de resultados que criamos para uma única conexão? (Estamos usando o pool de conexões)
  2. Existe uma maneira de configurar o número de objects de instrução / conjunto de resultados no database (como conexões)?
  3. É aconselhável usar o object statement / statementet de variável de instância em vez do método local / object de conjunto de resultados em um único ambiente encadeado?
  4. A execução de uma instrução preparada em um loop causa esse problema? (Claro, eu poderia ter usado o sqlBatch) Nota: pStmt é fechado quando o loop terminar.

    { //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. O que acontecerá se conn.createStatement () e conn.prepareStatement (sql) forem chamados várias vezes em um único object de conexão?

Edit1: 6. O uso do object de declaração de referência Weak / Soft ajudará na prevenção do vazamento?

Edit2: 1. Existe alguma maneira, posso encontrar todos os “statement.close ()” em falta no meu projeto? Eu entendo que não é um memory leaks. Mas eu preciso encontrar uma referência de declaração (onde close () não é executada) elegível para garbage collection? Alguma ferramenta disponível? Ou preciso analisá-lo manualmente?

Por favor me ajude a entender isso.

Solução

Para encontrar o cursor aberto no database Oracle para nome de usuário -VELU

Vá para a máquina ORALCE e inicie o sqlplus como sysdba.

 [oracle@db01 ~]$ sqlplus / as sysdba 

Então corra

 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'; 

Se possível, por favor leia minha resposta no final.

ORA-01000, o erro maximum-open-cursors, é um erro extremamente comum no desenvolvimento de database Oracle. No contexto do Java, isso acontece quando o aplicativo tenta abrir mais ResultSets do que os cursores configurados em uma instância do database.

Causas comuns são:

  1. Erro de configuração

    • Você tem mais threads em seu aplicativo consultando o database do que cursores no database. Um caso é onde você tem uma conexão e um conjunto de encadeamentos maior que o número de cursores no database.
    • Você tem muitos desenvolvedores ou aplicativos conectados à mesma instância de database (que provavelmente includeá muitos esquemas) e, juntos, você está usando muitas conexões.
    • Solução:

      • Aumentando o número de cursores no database (se os resources permitirem) ou
      • Diminuindo o número de encadeamentos no aplicativo.
  2. Vazamento de cursor

    • Os aplicativos não estão fechando ResultSets (no JDBC) ou cursores (em stored procedures no database)
    • Solução : os vazamentos do cursor são erros; Aumentar o número de cursores no database simplesmente atrasa a falha inevitável. Vazamentos podem ser encontrados usando análise de código estático , JDBC ou log no nível do aplicativo e monitoramento de database .

fundo

Esta seção descreve algumas das teorias por trás dos cursores e como o JDBC deve ser usado. Se você não precisa conhecer o plano de fundo, pode ignorar isso e ir direto para ‘Eliminando vazamentos’.

O que é um cursor?

Um cursor é um recurso no database que contém o estado de uma consulta, especificamente a posição em que um leitor está em um ResultSet. Cada instrução SELECT possui um cursor e os stored procedures PL / SQL podem ser abertos e usar quantos cursores forem necessários. Você pode descobrir mais sobre cursores no Orafaq .

Uma instância de database geralmente serve vários esquemas diferentes, muitos usuários diferentes, cada um com várias sessões . Para fazer isso, ele tem um número fixo de cursores disponíveis para todos os esquemas, usuários e sessões. Quando todos os cursores estão abertos (em uso) e o pedido vem em um novo cursor, a solicitação falha com um erro ORA-010000.

Encontrar e configurar o número de cursores

O número é normalmente configurado pelo DBA na instalação. O número de cursores atualmente em uso, o número máximo e a configuração podem ser acessados ​​nas funções Administrador no Oracle SQL Developer . De SQL, pode ser definido com:

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

Relacionando o JDBC na JVM aos cursores no DB

Os objects JDBC abaixo estão fortemente acoplados aos seguintes conceitos de database:

  • A Conexão JDBC é a representação do cliente de uma session do database e fornece transactions do database. Uma conexão pode ter apenas uma única transação aberta a qualquer momento (mas as transactions podem ser aninhadas)
  • Um ResultSet JDBC é suportado por um único cursor no database. Quando close () é chamado no ResultSet, o cursor é liberado.
  • Um CallableStatement do JDBC invoca um procedimento armazenado no database, geralmente escrito em PL / SQL. O procedimento armazenado pode criar zero ou mais cursores e pode retornar um cursor como um ResultSet JDBC.

O JDBC é thread-safe: Não há problema em passar os vários objects JDBC entre os threads.

Por exemplo, você pode criar a conexão em um thread; outro thread pode usar essa conexão para criar um PreparedStatement e um terceiro thread pode processar o conjunto de resultados. A única restrição importante é que você não pode ter mais de um ResultSet aberto em um único PreparedStatement a qualquer momento. Consulte O Oracle DB suporta várias operações (paralelas) por conexão?

Observe que uma confirmação de database ocorre em uma Conexão e, portanto, todos os DMLs (INSERT, UPDATE e DELETE) nessa conexão serão confirmados juntos. Portanto, se você quiser suportar várias transactions ao mesmo tempo, deverá ter pelo menos uma conexão para cada transação simultânea.

Fechando objects JDBC

Um exemplo típico de executar um ResultSet é:

 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 como a cláusula finally ignora qualquer exceção levantada pelo close ():

  • Se você simplesmente fechar o ResultSet sem o try {} catch {}, ele poderá falhar e impedir que o Statement seja fechado
  • Queremos permitir que qualquer exceção levantada no corpo da tentativa se propague ao chamador. Se você tiver um loop, por exemplo, criando e executando instruções, lembre-se de fechar cada instrução dentro do loop.

No Java 7, a Oracle introduziu a interface AutoCloseable, que substitui a maior parte do boilerplate do Java 6 por um belo acréscimo sintático.

Segurando objects JDBC

Objetos JDBC podem ser mantidos com segurança em variables ​​locais, instância de object e membros de class. Geralmente, é uma prática melhor:

  • Usar instância de object ou membros de class para reter objects JDBC que são reutilizados várias vezes durante um período mais longo, como Conexões e PreparedStatements
  • Use variables ​​locais para ResultSets, uma vez que estas são obtidas, colocadas em loop e, em seguida, fechadas normalmente dentro do escopo de uma única function.

Há, no entanto, uma exceção: Se você estiver usando EJBs ou um contêiner Servlet / JSP, precisará seguir um modelo de encadeamento restrito:

  • Apenas o Application Server cria encadeamentos (com os quais manipula solicitações de input)
  • Apenas o Application Server cria conexões (obtidas do pool de conexões)
  • Ao salvar valores (estado) entre as chamadas, você precisa ter muito cuidado. Nunca armazene valores em seus próprios caches ou membros estáticos – isso não é seguro em clusters e outras condições estranhas, e o Application Server pode fazer coisas terríveis em seus dados. Em vez disso, use beans com estado ou um database.
  • Em particular, nunca segure objects JDBC (Conexões, ResultSets, PreparedStatements, etc) em diferentes chamadas remotas – deixe o Application Server gerenciar isso. O Application Server não apenas fornece um pool de conexão, mas também armazena em cache suas PreparedStatements.

Eliminando vazamentos

Há vários processos e ferramentas disponíveis para ajudar a detectar e eliminar vazamentos de JDBC:

  1. Durante o desenvolvimento – pegar os bugs antecipadamente é de longe a melhor abordagem:

    1. Práticas de desenvolvimento: Boas práticas de desenvolvimento devem reduzir o número de erros em seu software antes que ele saia da mesa do desenvolvedor. Práticas específicas incluem:

      1. Par programação , para educar aqueles sem experiência suficiente
      2. Código analisa porque muitos olhos são melhores que um
      3. Teste de unidade, o que significa que você pode exercitar toda e qualquer base de código de uma ferramenta de teste que torne a reprodução de vazamentos trivial
      4. Use bibliotecas existentes para o pool de conexões, em vez de construir seu próprio
    2. Análise de Código Estático: Use uma ferramenta como os excelentes Findbugs para executar uma análise de código estático. Isso pega muitos lugares onde o close () não foi tratado corretamente. Findbugs tem um plugin para o Eclipse, mas também é executado de forma independente para one-offs, tem integrações no Jenkins CI e outras ferramentas de compilation

  2. Em tempo de execução:

    1. Holdability e comprometer

      1. Se a capacidade de retenção do ResultSet for ResultSet.CLOSE_CURSORS_OVER_COMMIT, o ResultSet será fechado quando o método Connection.commit () for chamado. Isso pode ser definido usando Connection.setHoldability () ou usando o método Connection.createStatement () sobrecarregado.
    2. Logging no tempo de execução.

      1. Coloque boas instruções de log no seu código. Estes devem ser claros e compreensíveis para que o cliente, a equipe de suporte e os colegas possam entender sem treinamento. Eles devem ser concisos e include a impressão dos valores de estado / interno das principais variables ​​e atributos, para que você possa rastrear a lógica de processamento. Um bom registro é fundamental para depurar aplicativos, especialmente aqueles que foram implantados.
      2. Você pode adicionar um driver JDBC de debugging ao seu projeto (para debugging – na verdade, não implemente-o). Um exemplo (eu não usei) é log4jdbc . Você então precisa fazer uma análise simples nesse arquivo para ver quais execuções não têm um fechamento correspondente. Contando a abertura e fecha deve destacar se existe um problema em potencial

        1. Monitorando o database. Monitore seu aplicativo em execução usando as ferramentas como a function SQL Monitor ‘Monitor SQL’ ou o TOAD da Quest . O monitoramento é descrito neste artigo . Durante o monitoramento, você consulta os cursores abertos (por exemplo, da tabela v $ sesstat) e revisa seu SQL. Se o número de cursores estiver aumentando e (mais importante) se tornar dominado por uma instrução SQL idêntica, você sabe que há um vazamento com esse SQL. Pesquise seu código e revise.

Outros pensamentos

Você pode usar WeakReferences para lidar com conexões de fechamento?

Referências frágeis e suaves são formas de permitir que você faça referência a um object de forma que permita que a JVM colete o referente a qualquer momento que julgar adequado (supondo que não haja cadeias de referência fortes para esse object).

Se você passar um ReferenceQueue no construtor para a Referência flexível ou fraca, o object será colocado no ReferenceQueue quando o object for GC’ed quando ocorrer (se ocorrer). Com essa abordagem, você pode interagir com a finalização do object e fechar ou finalizar o object naquele momento.

Referências fantasmas são um pouco mais estranhas; sua finalidade é apenas controlar a finalização, mas você nunca pode obter uma referência ao object original, portanto, será difícil chamar o método close () nele.

No entanto, raramente é uma boa ideia tentar controlar quando o GC é executado (o Weak, Soft e o PhantomReferences permitem que você saiba após o fato de o object ser enfileirado para o GC). Na verdade, se a quantidade de memory na JVM for grande (por exemplo, -Xmx2000m), você nunca poderá fazer o GC do object, e você ainda experimentará o ORA-01000. Se a memory da JVM for pequena em relação aos requisitos do seu programa, você poderá descobrir que os objects ResultSet e PreparedStatement são enviados imediatamente após a criação (antes que você possa ler a partir deles), o que provavelmente falhará em seu programa.

TL; DR: O mecanismo de referência fraco não é uma boa maneira de gerenciar e fechar objects Statement e ResultSet.

Estou adicionando um pouco mais de compreensão.

  1. Cursor é apenas sobre um object de declaração; Não é nem resultSet nem o object de conexão.
  2. Mas ainda temos que fechar o conjunto de resultados para liberar alguma memory oracle. Ainda assim, se você não fechar o conjunto de resultados que não será contado para o CURSORS.
  3. O object Instrução de Encerramento também fechará automaticamente o object do conjunto de resultados.
  4. Cursor será criado para toda a instrução SELECT / INSERT / UPDATE / DELETE.
  5. Cada instância do ORACLE DB pode ser identificada usando o oracle SID; Da mesma forma, o ORACLE DB pode identificar cada conexão usando o SID de conexão. Ambos SID são diferentes.
  6. Portanto, a session ORACLE não é nada além de uma conexão jdbc (tcp); que não é senão um SID.
  7. Se definirmos cursores máximos como 500, será apenas para uma session / conexão / SID do JDBC.
  8. Portanto, podemos ter muitas conexões JDBC com seu respectivo no de cursores (instruções).
  9. Uma vez que a JVM é encerrada, todas as conexões / cursores serão fechadas, OU JDBCConnection é fechado. CURSORES com relação a essa conexão será fechado.

Loggin como sysdba.

Em Putty (login da Oracle):

  [oracle@db01 ~]$ sqlplus / as sysdba 

No SqlPlus:

UserName: sys as sysdba

Defina o valor de session_cached_cursors como 0 para que ele não tenha cursores fechados.

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

Selecione o conjunto de validades OPEN_CURSORS existente por conexão no 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; 

Abaixo está a consulta para encontrar a lista SID / conexões com valores de cursor abertos.

  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' 

Use a consulta abaixo para identificar os sql’s nos cursores abertos

  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' 

Agora depure o código e aproveite !!! 🙂

Corrija seu código assim:

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

Você tem certeza de que está realmente fechando seus pStatements, conexões e resultados?

Para analisar objects abertos, você pode implantar um padrão de delegador, que envolve o código em torno de seus objects statemant, connection e result. Então você verá, se um object for fechado com sucesso.

Um exemplo 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"); } } } 

Se o seu aplicativo for um aplicativo Java EE em execução no Oracle WebLogic como o servidor de aplicativos, uma possível causa desse problema será a configuração Tamanho do Cache de Instrução no WebLogic.

Se a configuração Tamanho do Cache de Instrução para uma determinada fonte de dados for aproximadamente igual ou maior que a configuração máxima de contagem de cursores abertos do database Oracle, todos os cursores abertos poderão ser consumidos por instruções SQL armazenadas em cache pelo WebLogic, resultando no erro ORA-01000.

Para resolver isso, reduza a configuração Tamanho do Cache de Instrução para cada origem de dados WebLogic que aponta para o database Oracle para ser significativamente menor que a configuração de contagem máxima do cursor no database.

No Console Administrativo do WebLogic 10, a configuração Tamanho do Cache de Instrução para cada fonte de dados pode ser encontrada em Serviços (navegação à esquerda)> Origens de Dados> (fonte de dados individual)> guia Pool de Conexão.

consulta para encontrar o sql que abriu.

 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 

Eu enfrentei o mesmo problema (ORA-01000) hoje. Eu tive um loop for no try {}, para executar uma instrução SELECT em um database Oracle muitas vezes, (cada vez que alterar um parâmetro), e no final {} tive meu código para fechar Resultset, PreparedStatement e Connection como de costume . Mas assim que cheguei a uma quantidade específica de loops (1000), recebi o erro do Oracle sobre muitos cursores abertos.

Com base no post de Andrew Alcock acima, fiz alterações para que, dentro do loop, eu fechasse cada conjunto de resultados e cada instrução depois de obter os dados e antes de fazer o loop novamente, e isso resolvesse o problema.

Além disso, ocorreu exatamente o mesmo problema em outro loop de instruções de inserção, em outro database Oracle (ORA-01000), desta vez após 300 instruções. Novamente, foi resolvido da mesma forma, portanto, o PreparedStatement ou o ResultSet ou ambos, contam como cursores abertos até serem fechados.

Eu também havia enfrentado esse problema. A exceção abaixo costumava vir

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

Eu estava usando o Spring Framework com Spring JDBC para dao layer.

Meu aplicativo usado para vazar cursores de alguma forma e depois de alguns minutos ou mais, costumava me dar essa exceção.

Depois de muita debugging e análise, descobri que havia o problema com a Indexação, a Chave Primária e as Restrições Únicas em uma das Tabelas que estavam sendo usadas na Consulta que eu estava executando.

Meu aplicativo estava tentando atualizar as colunas que estavam erroneamente indexadas . Então, sempre que meu aplicativo estava atingindo a consulta de atualização nas colunas indexadas, o database tentava fazer a reindexação com base nos valores atualizados. Estava vazando os cursores .

Consegui resolver o problema fazendo Indexação Apropriada nas colunas que foram usadas para pesquisar na consulta e aplicar restrições apropriadas sempre que necessário.

Você definiu autocommit = true? Se não tentar isso:

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

Usar o processamento em lote resultará em menos sobrecarga. Veja o seguinte link para exemplos: http://www.tutorialspoint.com/jdbc/jdbc-batch-processing.htm

No nosso caso, estávamos usando o Hibernate e tínhamos muitas variables ​​referenciando a mesma entidade mapeada do Hibernate. Estávamos criando e salvando essas referências em um loop. Cada referência abriu um cursor e manteve-o aberto.

Descobrimos isso usando uma consulta para verificar o número de cursores abertos enquanto executamos nosso código, percorremos um depurador e comentamos seletivamente as coisas.

Quanto ao motivo pelo qual cada nova referência abriu outro cursor – a entidade em questão tinha collections de outras entidades mapeadas para ela e acho que isso tinha algo a ver com ela (talvez não apenas isso, mas em combinação com como configuramos o modo de busca e configurações de cache). O próprio Hibernate tem erros ao não fechar os cursores abertos, embora pareça que estes foram corrigidos em versões posteriores.

Como não precisávamos ter tantas referências duplicadas à mesma entidade, a solução era parar de criar e manter todas essas referências redundantes. Uma vez que fizemos isso, o problema desapareceu.

Esse problema ocorre principalmente quando você está usando o pool de conexão porque quando você fecha a conexão, essa conexão volta para o pool de conexão e todos os cursores associados a essa conexão nunca são fechados, pois a conexão com o database ainda está aberta. Portanto, uma alternativa é diminuir o tempo de conexão ociosa das conexões no pool, assim, sempre que a conexão ficar inativa por 10 segundos, a conexão com o database será fechada e a nova conexão será criada para ser colocada no pool.

Eu tive esse problema com minha fonte de dados no WildFly e no Tomcat, conectando-me a um Oracle 10g.

Descobri que, sob certas condições, a instrução não foi fechada mesmo quando o statement.close () foi invocado. O problema estava no driver Oracle que estávamos usando: ojdbc7.jar. Este driver é destinado ao Oracle 12c e 11g, e parece ter alguns problemas quando é usado com o Oracle 10g, então eu faço o downgrade para ojdbc5.jar e agora tudo está rodando bem.

Eu enfrentei o mesmo problema porque estava consultando o db por mais de 1.000 iterações. Eu usei tente e, finalmente, no meu código. Mas ainda estava recebendo erro.

Para resolver isso acabei de logar no oracle db e executei abaixo da consulta:

ALTER SYSTEM SET open_cursors = 8000 SCOPE = BOTH;

E isso resolveu meu problema imediatamente.