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

Ответ 1

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

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

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

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

  • Решение:

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

• Утечка курсора

  • Приложения не закрывают 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 с курсорами на DB

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

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

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

Например, вы можете создать соединение в одном потоке; другой поток может использовать это соединение для создания PreparedStatement, а третий поток может обрабатывать набор результатов. Единственное серьезное ограничение состоит в том, что вы не можете открыть более одного 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 {}, он может выйти из строя и запретить закрытие Заявления
• Мы хотим разрешить любое исключение, поднятое в теле try, распространяться на вызывающего. Если у вас есть цикл, например, создание и выполнение выражений, не забудьте закрыть каждое выражение в цикле.

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

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

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

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

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

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

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

Существует ряд процессов и инструментов для обнаружения и устранения утечек JDBC:

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

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

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

  • Анализ статического кода: используйте инструмент, отличный от Findbugs, чтобы выполнить статический анализ кода. Это забирает много мест, где функция close() не была правильно обработана. У Findbugs есть плагин для Eclipse, но он также работает автономно для одноразовых приложений, имеет интеграцию в Jenkins CI и другие инструменты сборки

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

  • Удержание и фиксация

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

  • Ведение журнала во время выполнения.

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

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

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

Другие мысли

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

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

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

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

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

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

Ответ 2

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

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

Loggin как sysdba.

В Putty (логин Oracle):

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

В SqlPlus:

Имя_пользователя: 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 для каждого соединения в 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;

Ниже приведен запрос на поиск списка 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'

Теперь отлаживаем код и наслаждаемся!!!:)

Ответ 3

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

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

Ответ 4

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

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

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

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

Ответ 5

Вы установили 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 

Ответ 6

чтобы найти открывшийся 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

Ответ 7

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

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

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

Ответ 8

Я тоже столкнулся с этой проблемой. Приведенное ниже исключение приходилось

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

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

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

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

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

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

Ответ 9

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

Ответ 10

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

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

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

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

Ответ 11

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

Ответ 12

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

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

Ответ 13

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

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

ALTER SYSTEM SET open_cursors = 8000 SCOPE = BOTH;

И это немедленно решило мою проблему.