java.sql.SQLException: – ORA-01000: превышены максимальные открытые курсоры

Я получаю исключение ORA-01000 SQL. Поэтому у меня есть некоторые вопросы, связанные с этим.

  1. Являются ли максимальные открытые курсоры точно связанными с количеством подключений JDBC или связаны также с объектами утверждения и результатов, которые мы создали для одного соединения? (Мы используем пул соединений)
  2. Есть ли способ настроить количество объектов statement / resultset в базе данных (например, соединения)?
  3. Целесообразно ли использовать объект экземпляра переменной / результат для объекта вместо объекта локального оператора / объекта результатов в одной streamовой среде?
  4. Вызывает ли выполнение подготовленного оператора в цикле эта проблема? (Конечно, я мог бы использовать sqlBatch) Примечание: pStmt закрывается после завершения цикла.

    { //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. Что произойдет, если conn.createStatement () и conn.prepareStatement (sql) вызываются несколько раз на одном объекте соединения?

Edit1: 6. Помогает ли использование объекта Weak / Soft reference statement в предотвращении утечки?

Edit2: 1. Есть ли способ, я могу найти все недостающие «statement.close ()» в моем проекте? Я понимаю, что это не утечка памяти. Но мне нужно найти ссылку справки (где close () не выполняется), подходящей для сбора мусора? Любой инструмент доступен? Или мне нужно анализировать его вручную?

Пожалуйста, помогите мне понять это.

Решение

Найти открытый курсор в Oracle DB для username -VELU

Перейдите на машину ORALCE и запустите sqlplus как sysdba.

 [[email protected] ~]$ sqlplus / as sysdba 

Затем запустите

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

Если возможно, прочитайте мой ответ в конце.

ORA-01000, ошибка максимального открытия-курсоров, является чрезвычайно распространенной ошибкой в ​​разработке базы данных Oracle. В контексте Java это происходит, когда приложение пытается открыть больше ResultSets, чем есть настроенные курсоры в экземпляре базы данных.

Общие причины:

  1. Ошибка конфигурации

    • У вас больше streamов в приложении, запрашивающих базу данных, чем курсоры в БД. В одном случае у вас есть соединение и пул streamов больше, чем число курсоров в базе данных.
    • У вас много разработчиков или приложений, подключенных к одному экземпляру БД (который, вероятно, будет включать в себя множество схем), и вместе вы используете слишком много соединений.
    • Решение:

      • Увеличение числа курсоров в базе данных (если позволяют ресурсы) или
      • Уменьшение количества streamов в приложении.
  2. Утечка курсора

    • Приложения не закрывают ResultSets (в JDBC) или курсоры (в хранимых процедурах в базе данных)
    • Решение . Утечки курсора – это ошибки; увеличение числа курсоров на БД просто задерживает неизбежный сбой. Утечки можно найти, используя статический анализ кода , JDBC или журналирование на уровне приложений и мониторинг базы данных .

Задний план

В этом разделе описывается теория курсоров и порядок использования JDBC. Если вам не нужно знать фон, вы можете пропустить это и перейти прямо к «Устранение утечек».

Что такое курсор?

Курсор – это ресурс в базе данных, который содержит состояние запроса, а именно позицию, в которой читатель находится в ResultSet. Каждый оператор SELECT имеет курсор, и хранимые процедуры PL / SQL могут открывать и использовать столько курсоров, сколько им требуется. Вы можете узнать больше о курсорах на Orafaq .

Экземпляр базы данных, как правило, обслуживает несколько разных схем , много разных пользователей, каждый из которых имеет несколько сеансов . Для этого у него есть фиксированное количество курсоров, доступных для всех схем, пользователей и сеансов. Когда все курсоры открыты (используются) и запрашивается запрос, в котором требуется новый курсор, запрос выходит из строя с ошибкой ORA-010000.

Поиск и настройка количества курсоров

Номер обычно настраивается администратором базы данных при установке. Количество используемых курсоров, максимальное количество и конфигурация могут быть доступны в функциях администратора в Oracle SQL Developer . Из SQL он может быть установлен с помощью:

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

Связывание JDBC в JVM с курсорами на БД

Объекты JDBC ниже тесно связаны со следующими концепциями баз данных:

  • JDBC Connection – это клиентское представление сеанса базы данных и обеспечивает транзакции базы данных. Соединение может иметь только одну транзакцию, открытую в любой момент (но транзакции могут быть вложенными)
  • JDBC ResultSet поддерживается одним курсором в базе данных. Когда в ResultSet вызывается функция close (), курсор отпускается.
  • JDBC CallableStatement вызывает хранимую процедуру в базе данных, часто написанную в PL / SQL. Хранимая процедура может создавать ноль или более курсоров и может возвращать курсор в виде набора результатов JDBC.

JDBC является streamобезопасным: вполне нормально передавать различные объекты JDBC между streamами.

Например, вы можете создать соединение в одном streamе; другой stream может использовать это соединение для создания PreparedStatement, а третий stream может обрабатывать набор результатов. Единственное серьезное ограничение состоит в том, что вы не можете открыть более одного ResultSet на одном PreparedStatement в любое время. Смотрите, поддерживает ли Oracle DB несколько (параллельных) операций для каждого соединения?

Обратите внимание, что фиксация базы данных происходит в соединении, и поэтому все DML (INSERT, UPDATE и DELETE) на этом соединении свяжутся вместе. Поэтому, если вы хотите одновременно поддерживать несколько транзакций, вы должны иметь хотя бы одно соединение для каждой параллельной транзакции.

Закрытие объектов JDBC

Типичным примером выполнения 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) { } } 

Обратите внимание, что в предложении finally игнорируется любое исключение, вызванное close ():

  • Если вы просто закроете ResultSet без try {} catch {}, он может выйти из строя и предотвратить закрытие Заявления
  • Мы хотим разрешить любое исключение, поднятое в теле попытки, распространяться на вызывающего. Если у вас есть цикл, например, создание и выполнение выражений, не забудьте закрыть каждое заявление в цикле.

В Java 7 Oracle внедрил интерфейс AutoCloseable, который заменяет большую часть шаблона Java 6 некоторым приятным синтаксическим сахаром.

Удержание объектов JDBC

Объекты JDBC можно безопасно хранить в локальных переменных, экземпляре объекта и членах classа. Как правило, лучше:

  • Использовать экземпляр объекта или членов classа для хранения объектов JDBC, которые многократно используются многократно в течение более длительного периода времени, таких как Connections и PreparedStatements
  • Используйте локальные переменные для ResultSets, поскольку они получены, зацикливаются, а затем закрываются, как правило, в пределах одной функции.

Однако есть одно исключение: если вы используете EJB или контейнер Servlet / JSP, вам необходимо следовать строгой модели streamов:

  • Только сервер приложений создает streamи (с которыми он обрабатывает входящие запросы)
  • Только сервер приложений создает соединения (которые вы получаете из пула соединений)
  • При сохранении значений (состояния) между вызовами вы должны быть очень осторожны. Никогда не храните значения в своих собственных кешах или статических членах – это небезопасно для кластеров и других странных условий, а сервер приложений может создавать ужасные вещи для ваших данных. Вместо этого используйте бобы с состоянием или базу данных.
  • В частности, никогда не держите объекты JDBC (Connections, ResultSets, PreparedStatements и т. Д.) По разным удаленным вызовам – пусть Application Server управляет этим. Сервер приложений не только предоставляет пул соединений, но также кэширует ваши PreparedStatements.

Устранение утечек

Существует ряд процессов и инструментов, помогающих обнаруживать и устранять утечки JDBC:

  1. Во время разработки – ловушка ошибок на ранней стадии является наилучшим подходом:

    1. Методы разработки. Хорошие методы развития должны уменьшить количество ошибок в вашем программном обеспечении до того, как он покинет рабочий стол разработчика. Конкретная практика включает:

      1. Паровое программирование , чтобы обучать тех, у кого нет достаточного опыта
      2. Кодовые обзоры, потому что многие глаза лучше, чем один
      3. Единичное тестирование, которое означает, что вы можете использовать любую и всю вашу базу кода из тестового инструмента, который делает воспроизведение утечек тривиальным
      4. Использовать существующие библиотеки для объединения пулов, а не создавать собственные
    2. Анализ статического кода: используйте инструмент, отличный от Findbugs, для выполнения статического анализа кода. Это забирает много мест, где функция close () не была правильно обработана. У Findbugs есть плагин для Eclipse, но он также работает автономно для одноразовых приложений, интегрируется в Jenkins CI и другие инструменты сборки

  2. Во время выполнения:

    1. Держать и фиксировать

      1. Если удержатель ResultSet – ResultSet.CLOSE_CURSORS_OVER_COMMIT, тогда ResultSet закрывается при вызове метода Connection.commit (). Это можно установить с помощью Connection.setHoldability () или с помощью перегруженного метода Connection.createStatement ().
    2. Ведение журнала во время выполнения.

      1. Поместите в свой код хорошие записи журнала. Они должны быть ясными и понятными, поэтому клиент, персонал поддержки и товарищи по команде могут понять без обучения. Они должны быть краткими и включать печать состояний / внутренних значений ключевых переменных и атрибутов, чтобы вы могли отслеживать логику обработки. Хорошая регистрация важна для отладки приложений, особенно тех, которые были развернуты.
      2. Вы можете добавить отладчик JDBC-драйвера в свой проект (для отладки – фактически не развертывайте его). Один пример (я его не использовал) – log4jdbc . Затем вам нужно сделать простой анализ этого файла, чтобы увидеть, какие из исполнений не имеют соответствующего закрытия. Подсчет открытий и закрытий должен указывать на наличие потенциальной проблемы

        1. Мониторинг базы данных. Контролируйте запущенное приложение с помощью таких инструментов, как SQL Monitor ‘Monitor SQL’ или Quest TOAD . Мониторинг описан в этой статье . Во время мониторинга вы запрашиваете открытые курсоры (например, из таблицы v $ sesstat) и просматриваете их SQL. Если число курсоров увеличивается, и (что наиболее важно), в котором доминирует один идентичный оператор SQL, вы знаете, что у вас есть утечка с этим SQL. Найдите свой код и просмотрите его.

Другие мысли

Можете ли вы использовать WeakReferences для обработки закрывающих соединений?

Слабые и мягкие ссылки – это способы, позволяющие вам ссылаться на объект таким образом, чтобы JVM мог мусор собирать референт в любое время, когда он сочтет нужным (при условии, что для этого объекта нет сильных ссылочных цепей).

Если вы передадите ReferenceQueue в конструкторе в мягкий или слабый Reference, объект помещается в ReferenceQueue, когда объект GC’ed, когда он встречается (если он вообще возникает). При таком подходе вы можете взаимодействовать с завершением объекта, и в этот момент вы можете закрыть или завершить объект.

Фантомные ссылки немного страннее; их цель состоит только в том, чтобы контролировать завершение, но вы никогда не сможете получить ссылку на исходный объект, поэтому будет трудно вызвать метод close ().

Тем не менее, редко рекомендуется пытаться контролировать, когда выполняется GC (Weak, Soft и PhantomReferences позволяют вам знать после того , как объект был помечен для GC). Фактически, если объем памяти в JVM большой (например, -Xmx2000m), вы никогда не сможете GC объект, и вы все равно будете испытывать ORA-01000. Если память JVM мала относительно требований вашей программы, вы можете обнаружить, что объекты ResultSet и PreparedStatement GCed сразу после создания (прежде чем вы сможете их прочитать), что, скорее всего, не даст вашей программе.

TL; DR: слабый механизм ссылок не является хорошим способом управления и закрытия объектов Statement и ResultSet.

Я добавляю немного больше понимания.

  1. Курсор – это только утверждение оператора; Он не является ни результатом, ни объектом соединения.
  2. Но все же нам нужно закрыть набор результатов, чтобы освободить память oracleа. Тем не менее, если вы не закрываете набор результатов, который не будет учитываться для CURSORS.
  3. Объект закрывающего оператора автоматически закрывает объект результатов.
  4. Курсор будет создан для всех операторов SELECT / INSERT / UPDATE / DELETE.
  5. Каждый экземпляр ORACLE DB может быть идентифицирован с использованием oracle SID; Аналогично, ORACLE DB может идентифицировать каждое соединение с использованием SID соединения. И SID разные.
  6. Таким образом, сеанс ORACLE – это не что иное, как соединение jdbc (tcp); который является всего лишь одним SID.
  7. Если мы установим максимальные курсоры как 500, то это будет только для одного сеанса / соединения JDBC / SID.
  8. Таким образом, у нас может быть много связей JDBC с его соответствующим отсутствием курсоров (операторов).
  9. Как только JVM будет завершена, все соединения / курсоры будут закрыты, ИЛИ JDBCConnection будет закрыто. КУРСОРЫ относительно этого соединения будут закрыты.

Loggin как sysdba.

В Putty (логин Oracle):

  [[email protected] ~]$ sqlplus / as sysdba 

В SqlPlus:

UserName: sys as sysdba

Установите значение session_cached_cursors равным 0, чтобы у него не было закрытых курсоров.

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

Выберите существующий набор значений OPEN_CURSORS для каждого соединения в БД

  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; 

Ниже приведен запрос на поиск списка SID / connections с открытыми значениями курсора.

  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' 

Используйте приведенный ниже запрос, чтобы идентифицировать sql в открытых курсорах

  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' 

Теперь отлаживайте код и наслаждайтесь !!! 🙂

Исправьте свой код следующим образом:

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

Вы уверены, что действительно закрываете свои pStatements, соединения и результаты?

Для анализа открытых объектов вы можете внедрить шаблон делегирования, который обертывает код вокруг объектов statemant, connection и result. Итак, вы увидите, если объект будет успешно закрыт.

Пример для: 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"); } } } 

Если ваше приложение является приложением Java EE, работающим в Oracle WebLogic в качестве сервера приложений, возможной причиной этой проблемы является параметр размера кэша Statement в WebLogic.

Если параметр размера кэша операторов для конкретного источника данных примерно равен или превышает значение максимальной длины подсчета курсора базы данных Oracle, то все открытые курсоры могут быть использованы кэшированными операторами SQL, которые остаются открытыми WebLogic, в результате чего в ошибке ORA-01000.

Чтобы устранить эту проблему, уменьшите параметр размера кэша операторов для каждого источника данных WebLogic, который указывает на то, что firebase database Oracle значительно меньше максимальной установки количества курсоров в базе данных.

В консоли администратора WebLogic 10 параметр размера кэша операторов для каждого источника данных можно найти в разделе «Службы» (слева)> «Источники данных» (отдельный источник данных)> вкладка «Пул подключений».

запрос на поиск открытого sql.

 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 

Сегодня я столкнулся с такой же проблемой (ORA-01000). У меня был цикл for в try {}, для выполнения инструкции SELECT в БД Oracle много раз (каждый раз при изменении параметра), а в finally {} у меня был код для закрытия Resultset, PreparedStatement и Connection как обычно , Но как только я достиг определенного количества циклов (1000), я получил ошибку Oracle о слишком большом количестве открытых курсоров.

Основываясь на записи Эндрю Алкока выше, я сделал изменения, так что внутри цикла я закрыл каждый набор результатов и каждое утверждение после получения данных и перед повторным циклом, и это решило проблему.

Кроме того, одна и та же проблема возникла в другом цикле вставных выражений в другой БД Oracle (ORA-01000), на этот раз после 300 операторов. Снова это было решено одинаково, поэтому либо PreparedStatement, либо ResultSet или оба, считаются открытыми курсорами, пока они не будут закрыты.

Я тоже столкнулся с этой проблемой.

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

Я использовал Spring Framework с Spring JDBC для уровня dao.

Мое приложение использовало для того, чтобы пропустить курсоры как-то и через несколько минут или около того, Это использовало, чтобы дать мне это исключение.

После большой тщательной отладки и анализа я обнаружил, что была проблема с индексированием, основным ключом и уникальными ограничениями в одной из таблиц , используемых в запросе i, который выполнялся.

Мое приложение пыталось обновить столбцы, которые были ошибочно проиндексированы . Таким образом, всякий раз, когда мое приложение ударяло запрос на обновление индексированных столбцов, firebase database пыталась выполнить повторное индексирование на основе обновленных значений. Это утечка курсоров .

Я смог решить проблему, выполнив правильное индексирование в столбцах, которые были использованы для поиска в запросе, и при необходимости применяя соответствующие ограничения.

Вы установили autocommit = true? Если не попробовать:

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

Использование пакетной обработки приведет к меньшему накладному расходам. См. Следующую ссылку для примера: http://www.tutorialspoint.com/jdbc/jdbc-batch-processing.htm

В нашем случае мы использовали Hibernate, и у нас было много переменных, ссылающихся на одну и ту же совокупность Hibernate. Мы создавали и сохраняли эти ссылки в цикле. Каждая ссылка открывала курсор и открывала его.

Мы обнаружили это, используя запрос для проверки количества открытых курсоров при запуске нашего кода, переходе с отладчиком и выборочном комментировании.

Что касается того, почему каждая новая ссылка открывала другой указатель – объект, о котором идет речь, имел коллекции других объектов, сопоставленных с ним, и я думаю, что это имело к этому отношение (возможно, не только это отдельно, но в сочетании с тем, как мы настроили режим выборки и настройки кеша). У самого Hibernate возникли ошибки при закрытии открытых курсоров, хотя похоже, что они были исправлены в более поздних версиях.

Поскольку на самом деле нам не нужно было так много дублирующих ссылок на одну и ту же сущность, решение было прекратить создавать и удерживать все эти избыточные ссылки. Как только мы это сделали, проблема, когда прочь.

Эта проблема возникает, когда вы используете пул соединений, потому что когда вы закрываете соединение, это соединение возвращается в пул соединений, и все курсоры, связанные с этим соединением, никогда не закрываются, поскольку соединение с базой данных все еще открыто. Таким образом, одна из альтернатив заключается в уменьшении времени простоя соединения в пуле, так что всякий раз, когда соединение простаивает в соединении в течение 10 секунд, соединение с базой данных будет закрыто, а новое соединение будет создано для пула.

У меня была эта проблема с моим источником данных в WildFly и Tomcat, подключающемся к Oracle 10g.

Я обнаружил, что при определенных условиях оператор не был закрыт даже при вызове statement.close (). Проблема заключалась в использовании драйвера Oracle, который мы использовали: ojdbc7.jar. Этот драйвер предназначен для Oracle 12c и 11g, и, похоже, у него есть некоторые проблемы, когда используется с Oracle 10g, поэтому я понижаюсь до ojdbc5.jar, и теперь все работает нормально.

Я столкнулся с той же проблемой, потому что я запрашивал db для более 1000 итераций. Я использовал try и, наконец, в своем коде. Но все еще возникала ошибка.

Чтобы решить эту проблему, я только что зашел в oracle db и выполнил запрос:

ALTER SYSTEM SET open_cursors = 8000 SCOPE = BOTH;

И это сразу решило мою проблему.

  • PreparedStatement со списком параметров в предложении IN
  • Почему драйвер JDBC должен быть помещен в папку TOMCAT_HOME / lib?
  • ResultSet to Pagination
  • Как работает PreparedStatement Java?
  • Разница между classами драйвера Oracle jdbc?
  • Получить запрос из java.sql.PreparedStatement
  • Как имитировать БД для тестирования (Java)?
  • NamedParameterJdbcTemplate vs JdbcTemplate
  • java.lang.ClassNotFoundException: com.mysql.jdbc.Driver в Eclipse
  • Кодировка символов JDBC
  • Использование подстановочного символа «like» в подготовленном сообщении
  • Interesting Posts

    Невозможно добавить элементы в NSMutableArray ivar

    Измените аудиовыход в зависимости от того, какой из них включен.

    Как синхронизировать контакты Thunderbird с контактами Gmail

    Как перезапустить Linux (Ubuntu) из командной строки?

    Можно ли сохранить настройки для Microsoft Paint?

    ProgressDialog не отображается, когда AsyncTask.get () называется

    Как добавить Distinct в Hibernate Criteria

    Захват двоичного вывода из Process.StandardOutput

    Указанный член типа не поддерживается в LINQ to Entities. Поддерживаются только инициализаторы, сущности и свойства навигации сущности

    Swift: как создать отметку времени и формат даты как ISO 8601, RFC 3339, часовой пояс UTC?

    Вычисление разницы во времени между двумя датами / временами из двух столбцов в Excel

    Как передать аргументы командной строки псевдониму оболочки?

    Как я могу выполнить полную установку Cygwin с помощью нового установщика?

    Заменить подстроку NSAttributedString на другую NSAttributedString

    Количество ошибок CRC жесткого диска Ultra DMA

    Давайте будем гением компьютера.