Which websites are using the Oracle database

Oracle

This article describes how to connect Tableau to an Oracle database and how to set up the data source.

Note: Starting with Tableau 2020.2, if you want to publish a data source or workbook with net_service_names to Tableau Server, see the changes described in the Knowledge Base article under How to Set an Oracle Connection to Use TNSNames.ora or LDAP.ora.

requirements

First, collect this connection information:

  • Name of the server hosting the database that you want to connect to and the Oracle service name and port or TNS name

  • Authentication method: Integrated authentication or username and password

  • Do you want to connect to an SSL server? You must configure the Oracle client before you can use SSL with Tableau.

    If you are using version 2020.2 or higher, see "Require SSL" for Oracle JDBC connections.

  • (Optional) Initial SQL statement that is run every time Tableau connects

Driver required

A driver is required for this connector to communicate with the database. The required driver may already be installed on your computer. If the driver is not installed on your computer, Tableau will display a message in the connection dialog with a link to the Download Driver page (Link opens in a new window). There you will find driver links and installation instructions.

Comparison of JDBC driver and OCI driver

Starting with version 2020.2, Tableau recommends using the JDBC driver (available on the Driver Download page (Link opens in a new window)) with this connector. The newer JDBC driver addresses some of the limitations associated with the OCI driver, such as no spatial support or Kerberos delegation on Linux.

Note: If you have problems using the JDBC driver for this connector, there is still an OCI driver available that you can use instead. For instructions, see the Driver Download page (link opens in a new window).

Establishing the connection and setting up the data source

  1. Start Tableau and under Connect, select Oracle. A comprehensive list of data connections is displayed when you select More under With a server. Then do the following:

    1. Enter the server name or TNS name.

      • When entering the server name, you must enter the Oracle service name and port number.

        Note: To find the correct service name, open the tnsnames.ora file (located in the ORACLE_HOME / network / admin directory by default) and look for the section net_service_name. Use the for service_name listed value.

      • Give Not Enter the Oracle service name and port number when entering the TNS name.

    2. Determine how you want to log into the server. Specify whether to use integrated authentication or a specific username / password combination.

      When connecting to an SSL server, select the Require SSL check box.

    3. (Optional) Select Initial SQL Dates to specify an SQL command to run at the start of every connection, such as: For example, when you open a workbook, refresh an extract, sign in to Tableau Server, or publish content to Tableau Server. For more information, see Executing Initial SQL.

    4. Select Sign In.

      If Tableau cannot connect, verify that your credentials are correct. If you still cannot connect, the computer cannot find the server. Contact your network administrator or database administrator.

  2. On the data sources page, do the following:

    1. (Optional) Select the default data source name at the top of the page, then enter a unique data source name to use in Tableau. For example, you can use a data source naming convention to help other users determine which data source to connect.

    2. From the Scheme drop-down list, select the search icon or type the scheme name in the text box, then select the search icon. Then select the scheme. Note: The search is case-sensitive.

    3. Under Table, select the search icon or type the table name, then select the search icon, and then select the table.

      Note: The search is case-sensitive.

      Tableau Desktop also supports connecting to Oracle table functions that appear in the left pane of the Data Source page under Stored Procedures.

      Starting with Tableau version 2020.2, you can connect to spatial columns. For more information, see Connect to Spatial Data in a Database (Link opens in a new window).

    4. Drag the table onto the work area, then click the sheet tab to start your analysis.

      Use custom SQL to connect to a specific query rather than the entire data source. For more information, see Connect to a Custom SQL Query.

Sign in to a Mac

If you are using Tableau Desktop on a Mac, enter a fully qualified domain name (for example, "mydb.test.ourdomain.lan") instead of a relative domain name (for example, "mydb" or "mydb.test").

Alternatively, you can add the domain to the list of search domains for the Mac computer so that you only need to provide the server name to connect. To update the list of search domains, go to System Preferences> Network> Advanced, then open the DNS tab.

Using Net Services Definitions in Tableau

To use your Net Services in Tableau you need to set either or as an environment variable. To set as the environment variable, use the full path of the directory that contains the file. To set as the environment variable, use the path to the main Oracle directory.

Migrating the workbook from the Oracle OCI connector to the Oracle JDBC connector

If you create a workbook using the Oracle OCI connector with SSL and later try to open it using the JDBC-based Oracle connector, Tableau cannot find the corresponding SSL certificate.

To migrate a workbook:

  1. Convert the certificate from PKCS12 format (used by Oracle Wallet) to JKS format. Use this command:

    keytool -importkeystore -srckeystore PFX_P12_FILE_NAME -srcstoretype pkcs12 -srcstorepass PFX_P12_FILE -srcalias SOURCE_ALIAS -destkeystore KEYSTORE_FILE -deststoretype jks -deststorepass -destNAMES

  2. Modify oracle.properties to customize this connection as follows:

    javax.net.ssl.trustStore =
    javax.net.ssl.trustStoreType = JKS
    javax.net.ssl.trustStorePassword =

See also

Other articles in this section