Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Henson
New Member

How can I Disable Connection Pooling for Oracle Queries?

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? 

1 ACCEPTED SOLUTION
v-binbinyu-msft
Community Support
Community Support

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.

View solution in original post

1 REPLY 1
v-binbinyu-msft
Community Support
Community Support

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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.