Breadcrumbs

Oracle

An Oracle connector is a database connector, unlike a storage connector such as GCS or S3. It currently supports creating on-platform, tabular assets, and cannot be used as an export destination.

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, or 2484 for encrypted connections using SSL/TLS)

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

  4. A user created for accessing the required data, with a 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 the target tables/views themselves

Listing catalog resources, the tables, views and materialized views you can choose from when creating an asset, uses Oracle's ALL_TABLES, ALL_VIEWS and ALL_MVIEWS dictionary views. These are available to any authenticated user via public synonym, so no elevated privileges such as DBA, SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY are required. Catalog listing only shows objects the connecting account can SELECT; anything your account cannot access simply won't appear.

If the connector logs in as the owning schema (the schema that owns the objects being connected to), no additional object-level grants are needed beyond the above. Note: schema listing doesn't yet reflect this. The schema picker may still show a schema your account has no grants in, which will then list no tables. This will be accounted for in a future release.

Note for platforms deployed using a Databricks workspace: reduced-privilege catalog listing above is confirmed for the standard Oracle connector. Please confirm with your Harbr account team whether this also applies to your environment before removing any existing DBA_* grants — a separate code path is used for some Databricks-deployment configurations and may have different requirements.

Supported versions: 19c, 23c

Create the Connector

  1. Click Manage on the navigation bar.

  2. Select Connectors to open the Manage Connectors screen.

  3. Click Create Connector, top right.

  4. Enter a Name for your connector, and an optional Description.

  5. Choose Type > Oracle.

  6. Add any Integration Metadata your setup requires (optional).

  7. Click Create. A connection test runs automatically; on success, Connection Test Status shows Successful.

  8. Click Close.

Configure your connector for Serverless Asset Create and Update

If enabled by your operator you can configure your connector to use serverless compute when creating and updating assets.

To enable this when creating your connector add the following as integration metadata:

 {
  "serverlessCompute": {
    true
  }
}

Federated Oracle Connector

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

Databricks Oracle federation has a documented known issue affecting Oracle instances 11.2.0.3.0 and later, where the source database's timezone isn't set to Etc/UTC. Queries against the federated connection then fail with ORA-01882: timezone region not found.

The documented fix is to set spark.databricks.connector.oracle.timezoneAsRegion. This can't be applied via the UI or SQL when creating the federated connection, since connection setup only accepts host, port, user and password, there's no way to pass additional JDBC properties at that point. Databricks Engineering needs to apply the workaround on your account directly.

Suggested wording for your request to Databricks:

"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: Databricks — Oracle federation limitations