Question: I am using the utl_tcp
package of Oracle to connect to remote server. As per my
architecture, I am opening multiple connections TCP in the same
Oracle session, and I hit a limitation of 16 connections with this
package, getting this ORA-30678 error.
ORA-30678 too many open connections
How to I
establish additional connections?
Answer:
The documentation says this on the ORA-30678 error, noting that you
have hit a hard limit of 16 connections:
ORA-30678 too many open connections
Cause: An attempt to open a connection failed
because too many are already open by this session. The number of
allowed connections varies as some may be in use through other
components which share the same pool of allowed connections.
Action: Retry after closing some other
connection. The number of connections supported is currently not
adjustable.
The utl_tcp package has a set
limit of 16 per session. You can't change it so you'll need to
change the design of your application. As shown in this
great article, be sure to always call an explicit quit
using utl_smtp.quit().
It appears that the utl_tcp package has a hard limit of
16 concurrent connections, but connections come-and-go quickly.
To avoid the ORA-30678 error you need to close some connections
and you can include 'utl_tcp.close_all_connections' at the
entry point to avoid this error.
Paulo Portugal notes:
If you are facing ORA-30678
errors when using UTL_TCP or UTL_SMTP packages :
ORA-30678: too many open
connections
ORA-06512: at "SYS.UTL_TCP", line 28
You really
need to close your connections after working with then as they are
not closed automatically. Here is a simple example of both
UTL_TCP and UTL_SMTP packages,
or you can close all connections using
utl_tcp.close_all_connections as mentioned before.:
--TCP example
declare
my_conn
utl_tcp.connection;
begin
--Open connection
my_conn := utl_tcp.open_connection(
remote_host => 'www.testlink.com',
remote_port => 80,
charset => 'AL16UTF16');
-- Explicitly close the
connection oppened
utl_tcp.close_connection(c => my_conn);
exception
when others then
raise_application_error (-20002,'An
error has occurred.');
end;
--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;
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
