2

I can't seem to find the way to avoid ORA-2391 "exceeded simultaneous SESSIONS_PER_USER limit" when working with DBeaver 4.3.3.1. Everytime my session goes idle and I need to recconect I'm getting this error. I'm not sure if this is a DBeaver issue or I am missing something in my Oracle connection configuration.

I already talked with DBA and he showed me that whenever I connect to my database DBeaver creates two sessions (two rows in table v$session) and when he tries to kill them, they hang out for a while until they finally go away and I can connect again.

I am sure I don't have any other session opened with any other tool.

My goal is to keep working with DBeaver since this tool has some amazing features, but without having to bother my DBA everytime I can't connect due to simultaneous sessions.

If there could be any way to stop DBeaver from creating more than one session for just one connection, I will appreciate any help.

PS:(I hope I'm clear with my question, English is not my native language)

Improve this question

2 Answers 2

Reset to default
4

I found the solution thanks to Balazs. There are a couple of options in the Preferences window that could generate multiple sessions.

  • The first one is at Preferences > General > Editors > SQL Editor, Connections group and "Open separate connection for each editor" option.
  • The other one is at Preferences > Database > Metadata, Metadata group and "Open separate connection for metadata read" option.

Both options must be unchecked in order to avoid creation of multiple sessions for same connection, therefore to avoid ORA-02391 error.

Improve this answer
1

ORA-02391 is caused by the SESSIONS_PER_USER limit in the profile your user belongs to.

create profile p1 limit SESSIONS_PER_USER 1;
create user u1 identified by u1 profile p1;
grant create session to u1;

SQL> connect u1/u1
Connected.

Now try logging in another session:

$ sqlplus /nolog

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

SQL>

I do not know DBeaver but I found this:

enter image description here

Or you could just have the limit increased for your user by the DBA:

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

PROFILE
-------
P1

SQL> select limit from dba_profiles where profile = 'P1' and resource_name = 'SESSIONS_PER_USER';

LIMIT
-----
1

SQL> alter profile p1 limit SESSIONS_PER_USER 5;

Profile altered.
Improve this answer
4
  • Hi Balazs, thank you for your quick answer, I have already talked to DBA about increasing session limit but it can't be done due to company policies. Besides, my guess is there is something wrong with DBeaver because I am currently working with SQL Developer and I am not having the same trouble as I am with DBeaver.
    – Wendy
    Feb 12, 2018 at 19:53
  • The thing is I really want to keep working with DBeaver because SQL Developer lacks some useful features DBeaver has. I want to mention that I can't see imgur images in my work computer, most of the sites are locked here.
    – Wendy
    Feb 12, 2018 at 20:00
  • @Wendy Picture was taken originally from here: user-images.githubusercontent.com/6398845/…
    – Balazs Papp
    Feb 12, 2018 at 20:18
  • Thank you again, I took a look at highlighted option from your image and it was unchecked, but you gave me the idea to go through all possible options in Preferences window that could involve connection settings and I did find another similar in Database > Metadata; option "Open separate connection for metadata read" was checked and I unchecked it. I proceeded to connect and now there is just one session open in v$session table, yay!. Let's see how it behaves when session invalidates again.
    – Wendy
    Feb 12, 2018 at 20:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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