Exceeding Sessions Per User Limit

[Anonymous]

I'm trying to refresh my Oracle connections via the Power BI Desktop application and keep getting an error for "ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit". How do I get Power BI to clear the sessions?

I've found other articles on this, however, they all relate to the Power BI Gateway, which I am not using. Please help! 

[v-qiuyu-msft]

Hi @Anonymous,

This is a error comes from Oracle side. Please troubleshoot this refer to below links: 

https://docs.oracle.com/cd/B10501_01/server.920/a96525/e2100.htm

https://confluence.atlassian.com/jirakb/how-to-fix-ora-02391-exceeded-simultaneous-sessions_per_user-limit-errors-in-jira-800867831.html

Best Regards,
Qiuyun Yu

[Anonymous]

It doesn't seem to be an Oracle issue - I can still query Oracle in other ways (DBeaver, Excel Power Query, etc.). Any suggestions?

[Anonymous]

Can't Microsoft fix Power BI so that it automatically closes the connections after it reuses them, OR have it reuse the existing connections? This is a real issue if trying to use Power BI with an Oracle datasource!

[Anonymous]

Any updates on this? I'm still encountering this issue. 

[Anonymous]

A work around that we have found reasonably successful, is a combination of changing settings within Power BI so that it doesn't use simultaneous connections, and being careful when in Power Query, to allow each preview to finish, before clicking the next action.

• On the settings, under Options > Current File > Data Load - unticking Import relationships from data sources on first load. (This appears to prevent Power BI spawning multiple queries to discover these relationships).
• Options > Current File > Data Load - untick Allow data preview to download in the background - preventing Power BI spawning multiple queries to the database to get previews.
• Options > Current File > Data Load - untick Enable parallel loading of tables - tells Power BI to only perform one query at a time.

The second part to this, is your behaviour when using Power Query. In combination to the above settings, you have to ensure that after each action you perform in Power Query (clicking to display a different step, performing different actions), that you always wait until the current preview is finished, before you click on the next action. This means that Power BI will spawn a query to the server, and once complete, close it back down to that single connection - which it will reuse).

If you click again while it's generating a preview and querying the Oracle data-source, this will spawn an additional query/connection - which may cause you to exceed the limit.

There may be some tweaks that can be made on the Oracle Server's end, but I'm not familiar with that side.

The explanation with this issue, is that each time Power BI queries the Oracle server, it will then spawn multiple processes on the server to fullfill that query, and each of those processes will get split again into multiple threads (e.g. one query, split into four processes, split into four threads each). 

Providing you do the above, once the query finishes, all those threads and processes get closed back down into that single connection. 

However, if you allow parallel loading, or click to perform another action, which requests another query before finishing the first - it will create an additional connection/query, which spawns multiple processes, and each of them are split into multiple threads. And that's where you hit your limits.

Apologies if my explanation is not technically correct - but this is my understanding from a user's perspective. YMMV