Oracle

Rather than a storage connector, that connects to a GCP or S3 bucket for example, an Oracle connector is referred to as a database connector. Currently, the Oracle connector is supported for the creation of platform, tabular assets and cannot be used as an export location.

Pre-requisites

To connect your Oracle database, you need:

1. Your database host's IP (e.g., 1.1.1.2) or domain (e.g., my.server.com)

2. Your database's port (usually 1521 for unencrypted connections and 2484 for encrypted connections using SSL/TLS)

3. Your database's system identifier (SID)/service name

4. A user created for accessing the required data and username and password for that user

5. Minimum Role/Permission:

   1. CREATE SESSION — allows the connector to authenticate and connect to the database

   2. SELECT on SYS.DBA_TABLES — required to list tables

   3. SELECT on SYS.DBA_VIEWS — required to list views

   4. SELECT on SYS.DBA_MVIEWS — required to list materialized views

   5. SELECT on the target tables/views themselves

Supported versions: 19c, 23c

Create the Connector

1. Click Manage on the Navigation bar.

2. Select Connectors to view the Manage Connectors screen

3. Click the Create Connector button at the top right

4. Enter a Name for your Connector and a Description (optional)

5. Choose Type > Oracle

6. Add any Integration Metadata needed for programmatic integration.

7. Click Create. Connection test will run and if successful, will show Connection Test Status as Successful.

8. Click Close.

Federated Oracle Connector

To enable Oracle federation in your Databricks environment, you will need to raise a request with your Databricks account team to apply a known-issue workaround on Serverless compute.

Below is proposed wording for that message to your Databricks account team:

Customer-to-Databricks request
Subject: Request to apply Oracle federation timezone workaround on Serverless compute
We are using Databricks Oracle federation and have encountered the documented known issue affecting Oracle instances 11.2.0.3.0 and later where the source database timezone is not set to Etc/UTC. Queries against the federated Oracle connection return:
ORA-01882: timezone region not found
The documented workaround is to set spark.databricks.connector.oracle.timezoneAsRegion. However, this property cannot be applied via the UI or SQL when creating the federated connection, since the connection creation options only accept host, port, user, and password. There is no mechanism to pass additional JDBC properties during connection setup.
Please could Databricks Engineering apply the Serverless-compute workaround on our account? We understand this workaround has been applied previously for other Databricks accounts hitting the same issue.

Reference: https://docs.databricks.com/aws/en/query-federation/oracle#limitations