What settings control the maximum number of connections per user in Oracle 12c

connection-poolingoracletimeout

We have some software that is throwing an exception "Pooled connection request timed out"

I know the reason for this and i know the fix. I just need to buy some time on the production server until i can release the software update.

Currently the software establishes about 640 simultaneous connections before it starts throwing this exception.

What database settings can i change which will increase the amount of simultaneous connections?

Best Answer

You might want to check the SESSIONS parameter, which controls the maximum number of sessions for the system:

SELECT name, value 
FROM v$parameter
WHERE name = 'sessions';

https://docs.oracle.com/database/121/REFRN/GUID-52804B5A-164F-44F3-8980-F2593B58D807.htm#REFRN10197

You can also verify that there isn't a limit configured via user profile. Query the DBA_PROFILES view to check if a value is assigned for the SESSIONS_PER_USER parameter associated with your user:

select profile, resource_name, limit
from dba_profiles
where resource_name = 'SESSIONS_PER_USER';

Info regarding profiles: https://docs.oracle.com/database/121/SQLRF/statements_6012.htm#SQLRF01310

Related Solutions

Sql-server – connection pooling, transactions, nested transaction and rollback

There is no such thing as nested transactions in any released version of SQL Server. You can say BEGIN TRANSACTION as many times as you want, but a ROLLBACK affects all of them (never mind what @@TRANCOUNT says - it only reflects nesting level).

From the documentation:

It is not legal for the transaction_name parameter of a ROLLBACK TRANSACTION statement to refer to the inner transactions of a set of named nested transactions. transaction_name can refer only to the transaction name of the outermost transaction. If a ROLLBACK TRANSACTION transaction_name statement using the name of the outer transaction is executed at any level of a set of nested transactions, all of the nested transactions are rolled back. If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all of the nested transactions, including the outermost transaction.

A demonstration:

CREATE TABLE dbo.foo(a INT);

BEGIN TRANSACTION;
  INSERT dbo.foo(a) SELECT 1;
  BEGIN TRANSACTION;
    INSERT dbo.foo(a) SELECT 2;
    BEGIN TRANSACTION;
    ROLLBACK TRANSACTION;

SELECT tc = @@TRANCOUNT;

SELECT a FROM dbo.foo;

DROP TABLE dbo.foo;

Results:

tc    
----
0

1 row(s) affected.

a
----

0 row(s) affected.

So, there is no way to only roll back part of a "nested" transaction - it's all or nothing. If you want previous parts of this transaction to commit even if later parts fail, then stop nesting, and commit the first transaction before starting the next one.

ORA : 12519 No appropriate Service Handler

http://docs.oracle.com/cd/E18283_01/network.112/e10836/concepts.htm

sales=
 (DESCRIPTION= 
  (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521)) 
  (CONNECT_DATA= 
     (SID=sales)
     (SERVICE_NAME=sales.us.example.com)
     (INSTANCE_NAME=sales)))

It looks like you haven't put the SID in your TNSNAMES.ORA.

Best Answer

Related Solutions

Sql-server – connection pooling, transactions, nested transaction and rollback

ORA : 12519 No appropriate Service Handler

Related Question