How can I Disable Connection Pooling for Oracle Queries?

[Henson]

Power BI uses the Oracle Client for Microsoft Tools, which I *think* is the unmanaged Oracle 19c driver. Unfortunatley, we're having an issue where connections are being left open and subsequent refreshes are failing. I'd like to disable connection pooling in Power BI, but I can't edit the connection string. Is there any workaround? 

[Anonymous · Accepted solution]

Hi @Henson ,

Power BI indeed utilizes the Oracle Client for Microsoft Tools (OCMT), and when connecting to Oracle databases, it uses the unmanaged Oracle Data Provider for .NET (ODP.NET). Connection pooling is a feature of ODP.NET designed to enhance performance by reusing active connections, but it can lead to the issue you're encountering if connections aren't being closed properly.

Unfortunately, Power BI does not provide a direct user interface option to edit the connection string parameters, including those related to connection pooling. However, there is a workaround that involves modifying the tnsnames.ora file used by the Oracle Client. Here's how you can attempt to disable connection pooling:

Locate the tnsnames.ora File: This file is typically found in the NETWORK\ADMIN directory within your Oracle Client installation path.
Edit the Connection Alias: Within the tnsnames.ora file, locate the alias used by Power BI to connect to your Oracle database. You'll want to add or modify the (POOLING = FALSE) parameter in the connection descriptor for your database alias.
For example:

MYDB_ALIAS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = mydbhost.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mydbservice)
    )
    (POOLING = FALSE)  # Add or ensure this parameter is set
  )

Save the Changes: After adding the (POOLING = FALSE) parameter, save your changes to the tnsnames.ora file.
Restart Power BI: Close and reopen Power BI Desktop to ensure it picks up the changes made to the tnsnames.ora file.

Please note, while this approach can disable connection pooling for the specified database alias, it may have performance implications for your applications that rely on this connection.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.