Oracle Database Connector: integrate with BigQuery, Redshift, ClickHouse, and more without manual pipelines

The Oracle Database connector has arrived on the Erathos platform.

Oracle Database is one of the most widely used relational databases in the enterprise world, supporting critical business applications, transaction processing, and large volumes of operational data. The problem is that this type of data often gets trapped inside the transactional database, far from the warehouse where the data team actually works, requiring manual replication, custom extraction scripts, and deep knowledge of Oracle’s internal structure to extract this data safely.

With Erathos's managed connector, you move data from Oracle directly to your destination warehouse with incremental synchronization, without writing or maintaining replication scripts.

How the Connection Works

Erathos connects to your Oracle database using three methods: Open Connection, Static IP, or SSH Tunnel, depending on how your database is exposed on the network.

The connection requires the following fields:

• db_host: hostname or IP address of your Oracle database
• db_port: listener port (1521 by default, 1522 on Oracle Autonomous Database)
• db_name: the Oracle service name (Erathos connects using service name, not SID). In Oracle Cloud, this is usually the fully qualified name, including the PDB name and the VCN DNS domain, for example, PDB.sub09061234567.myvcn.oraclevcn.com
• db_user and db_password: the credentials Erathos should use

Optionally:

• db_schema: restricts the extraction to a single schema. If left blank, Erathos discovers tables, views, and materialized views across all schemas visible to the provided credentials, excluding Oracle system schemas (SYS, SYSTEM, XDB, CTXSYS, among others)
• db_role: required only if the credentials require an elevated role, such as SYSDBA or SYSOPER. Leave blank for a standard, non-privileged connection

Erathos always attempts to connect via TCPS (encrypted connection) first and automatically falls back to standard TCP if the database does not have TCPS configured. No wallet file needs to be uploaded.

Connecting via Oracle Cloud (OCI)

The steps to gather the host, port, and service name depend on whether the database is an Autonomous Database or a DB System (VM, Bare Metal, or Exadata).

Autonomous Database (ADB-S / ADW / ATP): In the OCI console, go to Oracle Database → Autonomous Database, select the instance, click DB Connection, and open the Connection Strings tab. Use any of the listed profiles (e.g., _high or _medium) to fill in the host, port (1522), and the full service_name. Under Network, configure an Access Control List (ACL) to allow connections from the Erathos static IP or any IP. You can also disable mTLS on the same screen, as Erathos connects via TLS without requiring a client wallet.

DB Systems (Base Database / VM / Bare Metal / Exadata): Access DB Systems, select the system and the database, and copy the Host Name/IP and Port (1521 by default). For the full service name, if it does not appear directly, run SELECT SYS_CONTEXT('USERENV','SERVICE_NAME') FROM DUAL; in the database. Ensure that the VCN's security list or network security group allows inbound traffic on the database port from the Erathos static IP or any IP. If the DB System only has a private IP, use an SSH tunnel connection through a bastion host that Erathos can reach.

Recommended User and Permissions

Erathos only requires read access. We recommend creating a dedicated, read-only user:

sql

CREATE USER erathos_reader IDENTIFIED BY "<strong-password>";

-- Required to establish a connection
GRANT CREATE SESSION TO erathos_reader;

-- Allows Erathos to read table/column/primary key metadata
-- from data dictionary views (ALL_OBJECTS, ALL_TAB_COLUMNS,
-- ALL_CONSTRAINTS, ALL_CONS_COLUMNS)
GRANT SELECT_CATALOG_ROLE TO erathos_reader;

-- Allows Erathos to read data from tables/views
GRANT SELECT ANY TABLE TO erathos_reader;

If you prefer to restrict access to specific schemas or tables instead of granting SELECT ANY TABLE, grant SELECT explicitly on each object you wish to sync, and fill in the db_schema field so Erathos only inspects that schema:

sql

GRANT SELECT ON schema_name.table_name TO erathos_reader;

Without SELECT_CATALOG_ROLE (or equivalent grants on ALL_* views), Erathos cannot discover tables, columns, or primary keys, even if SELECT has already been granted on the tables themselves.

What You Can Do with Oracle Database as a Data Source

Centralize information. Consolidate Oracle data with other company data sources into a single warehouse.

Model data. Build data models for advanced and predictive analytics on top of already centralized transactional data.

Optimize business strategy. Understand how processes supported by Oracle are performing, without relying on manual reports extracted directly from the database.

Monitor performance. Track the evolution of metrics and key performance indicators (KPIs) using historical data centralized in the warehouse, rather than relying solely on the current snapshot of the transactional database.

Integrate data. Combine Oracle information with CRM, product, and other sources to gain more precise insights than the transactional database can deliver in isolation.

Supported destinations include BigQuery, Redshift, Databricks, ClickHouse, PostgreSQL, Supabase, Azure Synapse, and Amazon S3.

Frequently Asked Questions About the Oracle Database Connector

Does Erathos need admin access to the Oracle database? No. It is recommended to create a dedicated, read-only user with CREATE SESSION, SELECT_CATALOG_ROLE, and SELECT ANY TABLE (or SELECT restricted to specific schemas). SYSDBA access is only required in specific scenarios, configured via the optional db_role field.

Do I need to upload a wallet file to connect? No. Erathos connects via TCPS (encrypted) by default, with an automatic fallback to standard TCP, without requiring a client wallet upload.

Does the connector work with Oracle Autonomous Database on OCI? Yes, both Autonomous Database (ADB-S, ADW, ATP) and traditional DB Systems (VM, Bare Metal, Exadata) are supported, each with its own specific configuration process.

How do I connect if the Oracle database only has a private IP? Via an SSH tunnel, using a bastion host that Erathos can reach, configured with the ssh_host, ssh_user, ssh_password (or private SSH key), and ssh_port fields.

Which destinations can the Oracle Database connector sync data to? BigQuery, Redshift, Databricks, ClickHouse, PostgreSQL, Supabase, Azure Synapse, and Amazon S3.

Get Started Now

Create your Erathos account and connect your Oracle database to your warehouse in minutes. The complete documentation is available at docs.erathos.com/connectors/databases/oracle.