Troubleshooting ORA-02391: Exceeded Simultaneous SESSIONS_PER_USER Limit

Support
1

Hello,

We have recently encountered the ORA-02391 error, which indicates that the simultaneous SESSIONS_PER_USER limit has been exceeded. Our current database configuration allows up to 16 sessions per user, and unfortunately, increasing this limit is not an option for us.

To address this issue, we have tried adjusting the connector settings with the following custom properties:

  • Max Pool Size: 8
  • Incr Pool Size: 2

However, these settings did not resolve the problem. We then experimented with the following configuration:

  • Max Pool Size: 4
  • Incr Pool Size: 2
  • Connection Timeout: 300

While this setup works, it is not ideal for our needs. According to our knowledge, the default values for these settings are:

  • Max Pool Size: 100
  • Incr Pool Size: 5

Additionally, we have noticed that the error tends to occur more frequently with EasyMorph jobs that load more than approximately 10 data tables from the database simultaneously without incorporating a "Synchronize" action.

We are wondering if we might have overlooked something in our configuration. Additionally, we are planning to migrate to Oracle Cloud 19c soon. Could it be possible that earlier versions of Oracle handle property settings differently, leading to this issue?

Any insights or suggestions from the community would be greatly appreciated.

Thank you!

Best regards,

2

Hello @xChristophx,

Is it possible that another application is using the same username to connect to the database? In such a case, the "Synchronize" action might help, but only to some extent.

I just tested the connection pool with several projects with multiple parallel import actions, and the number of sessions stayed within the Max Pool Size limit at all times.

You can track the number of sessions with the following query:

SELECT username, program, status, COUNT(*) 
FROM v$session
WHERE username = 'USER_NAME' 
GROUP BY username, program, status

Have in mind that a single "Import from database" action might make up to three queries to the database, depending on the action's internal state and the target database (one for the columns list, one for the comments/annotations for the columns, and one for the actual data). Each query will open a separate connection, but it will close/release the connection right away.

Regarding the different behavior across Oracle versions, the connection pool is managed by the ODP.NET, which is built into EasyMorph. So I would expect significant changes with this issue after migration to Oracle Cloud 19c.

1 Like
3

Hi @andrew.rybka ,
i can confirm that the user account in question is exclusively assigned for use within EasyMorph. As it is a personal account, it is not utilized by any other applications.

I will attempt to track the sessions using the SQL query you provided. Should I gain any further insights from this, I will reach out again.

Thank you.