LLI Wiki — My Digital Knowledge Base

Installing ODP.NET for Power BI and checking an Oracle connection

Register the ODP.NET provider with OraProvCfg, then test the Oracle connection and listener.

Power BI connects to Oracle through the Oracle Data Provider for .NET (ODP.NET). This page describes how to register the provider on the workstation, then lists a few commands to check the connection and the listener.

Registering ODP.NET

  1. Add the assembly to the Global Assembly Cache (GAC):
C:\oracle\product\19.0.0\client_1\<ODP.NET_folder>\OraProvCfg.exe /action:gac /providerpath:C:\oracle\product\19.0.0\client_1\<ODP.NET_folder>\Oracle.DataAccess.dll
  1. Add the ODP.NET unmanaged client entries to machine.config:
C:\oracle\product\19.0.0\client_1\odp.net\bin\4\OraProvCfg.exe /action:config /force /product:odp /frameworkversion:v4.0.30319 /providerpath:C:\oracle\product\19.0.0\client_1\odp.net\bin\4\Oracle.DataAccess.dll
Option Purpose
/action:gac Adds the assembly to the GAC
/action:config Writes the provider entries to machine.config
/force Forces the configuration to be written
/product:odp Product: ODP.NET
/frameworkversion:v4.0.30319 Targeted .NET Framework version (4.x)

Testing the connection

From a command prompt with SQL*Plus available:

sqlplus "<user>/<password>@<host>:1521/<service>"

Checking the Oracle server

On Linux or Unix, the following commands check the processes, the environment and the listener configuration:

ps -ef | grep tnslsnr
ps -ef | grep pmon
env | grep ORA
lsnrctl status LISTENER
cat $ORACLE_HOME/network/admin/tnsnames.ora

Useful queries from SQL*Plus:

select * from v$listener_network;
select name from dba_services;

Auditing connections

The following query summarises, per user, the connections recorded in dba_audit_session, with the first and the last date.

select sys_context ('USERENV','server_host') server_host,du.username,os_username,userhost,terminal,count(*) as nbr ,max(timestamp) lastdate ,min(timestamp) firstdate
from dba_users du
  left outer join dba_audit_session das on das.username = du.username
group by du.username,os_username,userhost,das.terminal
Mastodon LLIMastodon SocialFosstodon