Ошибка ora 02391 - Не ошибается лишь тот, кто ничего не делает!

22265 views
1 min , 6 sec read
3

PROBLEM:

While connecting to the database, getting error:

SQL> connect test9/test9
ERROR:
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit

SOLUTION:

1. Check how many sessions were already existed for that user:

SELECT count(*) as connections,username FROM v$session where username=’&USER_NAME’ GROUP BY username;

SQL> SELECT count(*) as connections,username   FROM   v$session where username='&USER_NAME' GROUP  BY username;
Enter value for user_name: TEST9
old   1: SELECT count(*) as connections,username   FROM   v$session where username='&USER_NAME' GROUP  BY username
new   1: SELECT count(*) as connections,username   FROM   v$session where username='TEST9' GROUP  BY username

CONNECTIONS USERN
----------- -----
          2 TEST9

TEST9 user has currently 2 sessions.

2. Check the value of SESSIONS_PER_USER for this user

col username for a12
col profile for a19
col limit for a12
set lines 299
select a.username,b.PROFILE,b.RESOURCE_NAME,b.limit from dba_users a , dba_profiles b where a.profile=b.profile and b.RESOURCE_NAME=’SESSIONS_PER_USER’ and a.username=’&USER_NAME’;

Enter value for user_name: TEST9
old   1: select a.username,b.PROFILE,b.RESOURCE_NAME,b.limit from dba_users a , dba_profiles b where a.profile=b.profile and b.RESOURCE_NAME='SESSIONS_PER_USER' and a.username='&USER_NAME'
new   1: select a.username,b.PROFILE,b.RESOURCE_NAME,b.limit from dba_users a , dba_profiles b where a.profile=b.profile and b.RESOURCE_NAME='SESSIONS_PER_USER' and a.username='TEST9'

USERNAME     PROFILE             RESOURCE_NAME                    LIMIT
------------ ------------------- -------------------------------- ------------
TEST9        TEST                SESSIONS_PER_USER                2

We can see, the sessions_per_user allowed for this users in 2. To fix this user, increase the limit for SESSIONS_PER_USER in the profile.

Here the profile for the user is TEST

3. Alter the profile with higher SESSIONS_PER_USER value.

SQL> ALTER PROFILE TEST LIMIT SESSIONS_PER_USER 10;

Profile altered.

Now try to connect with the user.

Источник

Problem

Error’s from the JIRA logs indicate an ORA-02391 error is being reported. JIRA may be unreliable, or performing poorly and the behaviour may be intermittent.

This behaviour may be particularly obvious when completing tasks that involve many queries to the JIRA database.

The following appears in the atlassian-jira.log

java.sql.SQLException: ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit

Diagnosis

Environment

JIRA is connected to an Oracle 11g or Oracle 12c Database.

Diagnostic Steps

The Oracle Alert Log will be reporting similar ORA-02391 errors.
Attempting to connect to the Oracle Database as the user specified in the dbconflg.xml file, may not be permitted with the same error.
Connecting to the JIRA as a DBA user and running the following will show a large number of connections to the database by the JIRA USER:
```
SELECT count(*) as connections, 
       username 
FROM   v$session 
GROUP  BY username 
ORDER  BY username; 
```

Checking the Limits for the affected user shows the user is approaching the maximum connection limit (replace <JIRA_DATABASE_USER> with the username in your dbconfig.xml file:

SELECT DISTINCT username, 
                profile, 
                resource_name, 
                limit
FROM   dba_profiles 
       NATURAL JOIN dba_users 
WHERE  resource_name = 'SESSIONS_PER_USER' 
       AND username = '<JIRA_DATABASE_USER>';

Cause

The total number of database connections to the Oracle Database by the specified user has reached the maximum, and therefore no more connections can be established for the current user.

Resolution

Ensure the maximum pool size in the JIRA instance’s dbconfig.xml file is not greater than the SESSIONS_PER_USER limit in Oracle. To do this:
1. Check the dbconfig.xml under the property: <pool-max-size>. This value should be no smaller than the values documented in:
2. Check the SESSIONS_PER_USER setting is equal or higher than the maximum pool size
  1. If this value is low, increase this limit or set the limit to UNLIMITED from the Oracle Database.
3. Confirm that no other applications are connecting to the Oracle database using the same user.

Last modified on Mar 30, 2016

Related content

No related content found

Источник

Is there a query which I can use to grab the amount of sessions I can use concurrently? I am threading some database connections and getting the error:

ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit

How can I get the value for this limit?

jarlh

41.9k8 gold badges45 silver badges63 bronze badges

asked Sep 11, 2015 at 14:17

start with this query to find out how many sessions you are using

select count(*) from v$session where username='YourUser';

then find out how many you are allowed in the profile for your user

select profile from dba_users where username ='YourUser';

finally

select PROFILE, LIMIT
from dba_profiles
WHERE RESOURCE_NAME = 'SESSIONS_PER_USER'
AND PROFILE = 'YourUserProfile';

and the fix

Alter profile YourUserProfile  limit SESSIONS_PER_USER 100;

Limits are in place for a reason as each session consumes resources. If you increase the limit some careful planning is in order for production systems so you don’t run out of memory.

Jeromy French

11.7k19 gold badges76 silver badges129 bronze badges

answered Sep 11, 2015 at 14:25

kevinskiokevinskio

4,4331 gold badge21 silver badges36 bronze badges

It’s profile setting

SELECT * FROM DBA_PROFILES WHERE RESOURCE_NAME = 'SESSIONS_PER_USER';

You can change it using

ALTER PROFILE <profile name> LIMIT SESSIONS_PER_USER <number>;

ALTER PROFILE <profile name> LIMIT SESSIONS_PER_USER UNLIMITED;

answered Sep 11, 2015 at 14:24

HusqvikHusqvik

5,6491 gold badge18 silver badges29 bronze badges

Get the PROFILE for that user

select profile from dba_users where username = :who;

Then get the resource limit for that profile

SELECT P1.LIMIT AS "Concurrent Sessions (Per User)"
FROM   DBA_PROFILES P1
WHERE  P1.PROFILE       = :PROFILE
   AND P1.RESOURCE_NAME = 'SESSIONS_PER_USER';

In SQL Developer, open the DBA panel and browse the users & profiles under ‘security’

answered Sep 11, 2015 at 14:42

thatjeffsmiththatjeffsmith

20.1k5 gold badges37 silver badges117 bronze badges

While the other solutions provide statements on altering the user session limit, the diagnostic queries did not work for me, although I am a complete beginner and maybe performed it incorrectly. The following queries from Atlassian helped me.

For checking number of sessions per user, so you can see which users are nearing their limit:

SELECT count(*) as connections, 
       username 
FROM   v$session 
GROUP  BY username 
ORDER  BY username;

and to check what that limit is set to be:

SELECT DISTINCT username, 
                profile, 
                resource_name, 
                limit
FROM   dba_profiles 
       NATURAL JOIN dba_users 
WHERE  resource_name = 'SESSIONS_PER_USER';

From there, the rest of the linked site or other solutions here can presumably help with altering the limit to your needs.

answered Jul 16, 2020 at 5:38

BlaisemBlaisem

5356 silver badges17 bronze badges

Источник

I’m logging in remotely to my school’s oracle server. Apparently I’ve exceeded the number of simultaneous connections and get the error ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit

I don’t have admin privileges. Does anyone know how I can end my current sessions without admin privileges?

Ollie

17k7 gold badges47 silver badges59 bronze badges

asked Dec 10, 2011 at 20:24

You don’t tell us what sql client you are using.

You can only end sessions started from your machine by stopping the client processes. But this could result in idle sessions that are not disconnected by the server. This is a DBA responsibility.

If you are starting parrellel sessions (from jdbc connection pool for instance) you should limit the parrallel sessions.

But the problem could be your fellow students loggin in with the same user account as you. DBA should raise SESSION_PER_USER or create sepparate users for each student.

answered Dec 11, 2011 at 20:40

Источник

May 3, 2021

I got ” ORA-02391 Exceeded simultaneous session_per_user limit ” error in Oracle database.

ORA-02391 Exceeded simultaneous session_per_user limit

Details of error are as follows.

ORA-02391:   exceeded simultaneous SESSIONS_PER_USER limit

Cause:   An attempt was made to exceed the maximum number of concurrent sessions allowed by the SESSIONS_PER_USER clause of the user profile.

Action:   End one or more concurrent sessions or ask the database administrator to increase the SESSIONS_PER_USER limit of the user profile.

exceeded simultaneous SESSIONS_PER_USER limit

This ORA-02391 errors are related with the attempt was made to exceed the maximum number of concurrent sessions allowed by the SESSIONS_PER_USER clause of the user profile.

The owner of the job is assigned to unlimited SESSIONS_PER_USER profile:

SQL> select profile from dba_users where username = 'USER1';

PROFILE
------------------------------
APPLICATION_USER


SQL> select PROFILE, RESOURCE_NAME, RESOURCE_TYPE, LIMIT from dba_profiles where PROFILE = 'APPLICATION_USER'

PROFILE              RESOURCE_NAME            RESOURCE_TYPE   LIMIT
-------------------- ------------------------ --------------- ---------------
APPLICATION_USER     COMPOSITE_LIMIT          KERNEL          DEFAULT
APPLICATION_USER     SESSIONS_PER_USER        KERNEL          UNLIMITED

End one or more concurrent sessions or ask the database administrator to increase the SESSIONS_PER_USER limit of the user profile.

1. Assign SYS a profile with sufficient SESSIONS_PER_USER, e.g. unlimited

2. Increase SESSIONS_PER_USER for the profile assigned to SYS, until errors are no longer reported

Check your user limit as follows.

select PROFILE, LIMIT from dba_profiles WHERE RESOURCE_NAME = 'SESSIONS_PER_USER' AND PROFILE = 'PROFILE_NAME';

SESSIONS_PER_USER Parameter

Then you can limit the profile’s limit as follows.

alter profile PROFILE_NAME limit SESSIONS_PER_USER 150;

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

1,790 views last month, 6 views today

Источник

PROBLEM:

SOLUTION:

Problem

Diagnosis

Cause

Resolution

Ensure the maximum pool size in the JIRA instance’s dbconfig.xml file is not greater than the SESSIONS_PER_USER limit in Oracle. To do this:

Related content

ORA-02391 Exceeded simultaneous session_per_user limit

exceeded simultaneous SESSIONS_PER_USER limit

SESSIONS_PER_USER Parameter

Do you want to learn Oracle Database for Beginners, then read the following articles.

Ensure the maximum pool size in the JIRA instance’s `dbconfig.xml` file is not greater than the SESSIONS_PER_USER limit in Oracle. To do this: