2

I have a leak somewhere in the number of UTL_SMTP connections being made and closed. There is an underlying limit of 16 from its reliance on UTL_TCP.

I'd like to be able to monitor the connection usage to find the problem.

Where can I get a count of the number of open UTL_TCP connections?

1

UTL_MAIL strongly rely on TCP (Network) and the number of active TCP connections that can be open for a session is 16, per documented Bug 8262564. Bug 8262564 got fixed in Oracle 11g 11.2.0.1 or later and also on Oracle 11g 11.1.0.7 as one-off patches. Bug 8262564 is quoted as reference to state that the number of active TCP connections that can be open for a session is 16. While UTL_MAIL does not have restrictions but the TCP that serves as the base for Mail related packages such as UTL_MAIL and UTL_SMTP does have an active session limit of 16.

You say: "the number of UTL_SMTP connections being made and closed". Are you sure about the closing part? When you open a mail connection, you have to explicitly close it:

--SMTP example
declare
my_conn utl_smtp.connection;

begin
-- open smtp connection
my_conn := utl_smtp.open_connection(
host => 'www.testlink.com',
port => 25);

-- Explicitly close the connection oppened
utl_smtp.quit(c => my_conn);

exception
when others then
raise_application_error (-20002,'An error has occurred.');

end;

Please, note that If UTL_MAIL throws an exception and emails are resend from the exception, the smtp connections are lift open and after 15 successive exceptions UTL_MAIL throws errors. Connection is getting closed only in those cases when UTL_MAIL.SEND succeeds without any error. Since everything related to a connection is done within the UTL_MAIL package it is not possible to close the connection from outside the package. I do not know a v$ to count open smpt/tcp connections for an Oracle session. I think you can do it by yourself, for example using the following code:

set serveroutput on 
alter session set smtp_out_server='<smtp_server_name:25>'; 

declare 
cnt number := 1; 
cnt1 number; 
begin 
for i in 1..20 loop 
begin 
UTL_MAIL.send('valid_email@domain.com','invalid_email@domain.com',NULL,NULL,'testing invalid email','testing invalid email' ); 
Exception 
When Others then 
UTL_MAIL.send('valid_email@domain.com','valid_email@domain.com',NULL,NULL,'send from exp block','send from exp block'); 
dbms_output.put_line(cnt); 
cnt := cnt + 1; 
end; 
end loop; 
End; 
/

I found this code on Oracle Metalink knowledge base. After each error, you increase a counter to monitor the number of open connections. Regards Giovanni

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.