Кому не интересна история, а нужен конкретный совет, топают в Решение.
С четверга начали появляться сессии, активные по многу часов, вернее пока не грохнешь, а грохнуть через ALTER SYSTEM KILL SESSION не удавалось, так и оставались висеть со статусом KILLED. В пятницу пользователи начали звонить и говорить, что при попытке выполнить функцию "Бла-бла-бла" получают зависшее приложение. Ну делать нечего, надо решать быстро, благо пользователей немного, сделал STARTUP FORCE и вроде все заработало. Но это костыль, который пользовать часто нельзя, а лучше вообще не пользовать.
Небольшое лирическое отступление... Система построена для выполнения данной функции примерно так: сервер приложений (IIS) <-> основной инстанс <-> вспомогательный <-> масса ftp-серверов. Вспомогательный инстанс заведен на сервере, расположенном в двух сетках и используется исключительно для передачи данных между сетками (так уж была придумана секьюрность еще до моего прихода). На вспомогательном инстансе используется указанный импровизированный ftp-клиент c Oracle-Base. Написана пара функций, которые возвращают содержимое необходимых файлов. Доступ к этим функциям выполняется через DB Link с основного инстанса.
Так вот, работало оно работало, работало оно работало, и тут бац! и перестало работать. Бывает. Начали искать проблему у себя, где может быть зацикливание, deadlocks и т.п. (в логах то было ORA-02080). В общем пришел к тому (откопал на SQL.RU), что эта ошибка выдается нам, если очень долго читаются/ожидаются данные через линку. Полезли вручную на FTP, оказалось реально проблема с ftp-сервером. Однако было непонятно, почему сессия вешается в статусе ACTIVE, а не отваливается по таймауту. Ограничивали даже время жизни соединения в приложении, но все равно сессии на сервере оставались даже после того как приложение завершало свою работу по таймауту.
Тут как назло починили FTP... Воспроизвести ошибку нереально...
Тут полез по до сих пор непонятной мне причине в пакет ftp, в функцию login и обнаружил, что при создании соединения в UTL_TCP.OPEN_CONNECTION не передается таймаут. По умолчанию он установлен в NULL, что означает, что он бесконечен и ошибка по таймауту не вывалится никогда.
Решение
Вот решил я подправить этот пакет для возможности установки таймаута, чего и вам советую.
В спецификации пакета FTP делаем следующие изменения:
FUNCTION login (p_host IN VARCHAR2,
p_port IN VARCHAR2,
p_user IN VARCHAR2,
p_pass IN VARCHAR2,
p_timeout IN NUMBER := NULL)
RETURN UTL_TCP.connection;
FUNCTION login (p_host IN VARCHAR2,
p_port IN VARCHAR2,
p_user IN VARCHAR2,
p_pass IN VARCHAR2,
p_timeout IN NUMBER := NULL)
RETURN UTL_TCP.connection IS
-- --------------------------------------------------------------------------
l_conn UTL_TCP.connection;
BEGIN
g_reply.delete;l_conn := UTL_TCP.open_connection(p_host, p_port, tx_timeout => p_timeout);
get_reply (l_conn);
send_command(l_conn, 'USER ' // p_user);
send_command(l_conn, 'PASS ' // p_pass);
RETURN l_conn;
END;