Oracle Database Source

Connect Oracle Database as a source to replicate tables from an Oracle service into your warehouse. Supaflow supports full refresh and cursor-based incremental sync for tables in the Oracle schema associated with the connecting user.

For an overview of capabilities and use cases, see the Oracle Database connector page. To run pipelines natively inside Snowflake, see Snowflake Native ETL.

Prerequisites

Before you begin, ensure you have:

• An active Oracle Database service reachable from Supaflow
• Network connectivity between Supaflow and your Oracle listener
• An Oracle user with CREATE SESSION so Supaflow can log in
• Read access to the tables you want to sync
• Firewall rules that allow inbound connections from Supaflow, if your database is not privately connected
• The Oracle service name for the database service you want to connect to
• A reliable cursor column on tables you want to sync incrementally

Allow Supaflow Network Access

If your Oracle database restricts inbound connections, add the Supaflow IP to your firewall rules:

18.214.240.61

Oracle Database commonly listens on TCP port 1521. Supaflow asks for Host, Port, and Service Name separately; do not paste a full connection string, SID, TNS alias, wallet name, or TNS descriptor into the Service Name field. See Oracle's Easy Connect documentation for Oracle's host, port, and service-name terminology.

Prepare Oracle Access

For the current connector, connect as the Oracle schema/user whose tables you want to sync. Oracle users need CREATE SESSION to log in:

GRANT CREATE SESSION TO your_user;

When your Oracle security model requires explicit grants for existing tables, grant read access to those tables:

GRANT SELECT ON schema.table_name TO your_user;

Replace your_user, schema, and table_name with your Oracle user and table names. A cross-schema SELECT grant controls read permission, but the current Supaflow source does not expose a Schema field; table discovery is based on the Oracle schema associated with the connecting user. See Oracle's CREATE USER and GRANT documentation for account and privilege syntax.

What Gets Synced

Supaflow discovers tables in the Oracle schema associated with the connecting user. Tables are available for selection when the configured user has permission to read them.

The Oracle source is scoped to one Oracle service connection per source. To sync from another Oracle service, create another Supaflow source.

When Oracle reports a schema, Supaflow includes it in the object namespace so you can distinguish objects in the pipeline UI.

The current connector discovers tables. Views, materialized views, and synonyms are not exposed as supported objects on this source page.

Sync Modes

Full refresh: On each sync, all rows are read from the selected table. Use full refresh for small tables, lookup tables, or tables without a reliable cursor column.

Incremental sync: Only rows in the selected cursor window are fetched. Use incremental sync for tables with a reliable Oracle date or timestamp-style column that advances when rows are inserted or updated.

Supaflow tracks cursor positions between syncs. You can also configure a lookback period so each incremental run re-reads a short window before the last cursor position, which helps capture late-arriving writes.

The Oracle connector does not use Oracle CDC, redo logs, LogMiner, GoldenGate, XStream, or Flashback for change capture. It does not detect hard deletes from the source. If a table does not have a reliable cursor column, use full refresh.

Schema Discovery

Supaflow reads table metadata from the connected Oracle schema to discover table names, column names, data types, and primary keys. Schema metadata is refreshed according to the Schema Refresh Interval setting.

New tables and columns appear after a schema refresh and can then be selected in pipelines.

Configuration

Step 1: Connection

Host*

Oracle database hostname or IP address
Example: db.example.com or 192.168.1.100

Port

Port on which Oracle Database is listening
Default: 1521

User*

Oracle database username
Example: app_schema

Password

Password for the Oracle user
Stored encrypted

Service Name*

Oracle service name to connect and sync
Example: ORCLPDB1 or FREEPDB1
Use a service name, not a SID, TNS alias, wallet name, or full connection string

---

Step 2: Sync Settings (Optional)

Lookback Period (seconds)

Number of seconds to re-fetch before the last cursor position on incremental syncs
Default: 0 (no lookback)
Range: 0 to 86400

---

Step 3: Advanced Settings (Optional)

Schema Refresh Interval

Interval in minutes for schema metadata refresh
0 = refresh before every pipeline execution
-1 = disable automatic schema refresh
Default: 60 (Range: -1 to 10080)

---

Step 4: Test & Save

After configuring all required properties, click Test & Save to verify the connection and save the source.

Performance and Source Load

Oracle syncs run SQL reads against the selected source tables. Initial syncs and full refreshes read every selected row, so they can add noticeable load on busy databases.

For large production tables:

• Use incremental sync when a reliable date or timestamp cursor is available
• Schedule large initial loads and full refreshes during off-peak hours
• Review Oracle connection, session, and resource controls for the Supaflow user
• Keep pipeline selection focused on the tables and columns you need

Troubleshooting

Common issues and their solutions:

Connection refused or timeout

Problem:

• Cannot connect to the Oracle database
• "Connection refused" or network timeout error
• Test connection takes too long

Solutions:

1. Verify network access:
   • Add Supaflow IP 18.214.240.61 to allowed inbound rules
   • Confirm inbound traffic is allowed on your Oracle port, usually 1521
2. Check the connection values:
   • Confirm Host and Port match your Oracle listener
   • Confirm Service Name is the Oracle service name, not a SID, TNS alias, wallet name, or full connection string
3. Use private connectivity when required:
   • If your database is not exposed publicly, connect through your approved private deployment or network path

---

Authentication failed

Problem:

• Invalid username or password
• User cannot log in to the Oracle service
• Credentials work in another tool but not from Supaflow

Solutions:

1. Verify the User, Password, and Service Name.
2. Confirm the user can create a database session:

   GRANT CREATE SESSION TO your_user;

3. Test from an allowed host with Oracle Easy Connect:

   sqlplus your_user@//your_host:1521/your_service_name

---

Permission denied or tables missing

Problem:

• Connection succeeds, but expected tables are missing
• Permission errors during schema discovery or sync

Solutions:

1. Connect as the Oracle schema/user whose tables you want to sync.
2. Grant SELECT on specific existing tables when required:

   GRANT SELECT ON schema.table_name TO your_user;

3. Refresh schema in Supaflow:
   • Set Schema Refresh Interval to 0
   • Save and test the source again
4. Remember the current connector discovers tables, not views, materialized views, or synonyms.
5. Do not rely on cross-schema grants alone to make another schema's tables appear if the connecting user is associated with a different schema.

---

Incremental sync misses expected rows

Problem:

• Incremental sync completes, but recent inserts or updates are missing
• Rows created or updated out of order are not picked up

Solutions:

1. Use a reliable cursor column:
   • Choose a date or timestamp-style column that changes whenever a row changes
   • If no such column exists, use full refresh
2. Increase the lookback period:
   • Use Lookback Period to re-read a short window before the previous cursor position
   • This is useful when writes can arrive slightly out of order
3. Do not rely on incremental sync for hard deletes:
   • The Oracle connector does not use CDC, redo logs, LogMiner, GoldenGate, XStream, or Flashback
   • Hard deletes are not detected by cursor-based incremental sync

---

Wallet, TCPS, or certificate-required connections

Problem:

• Your Oracle environment requires Oracle Wallet, TCPS, client certificates, or a TNS descriptor
• Connection fails even though Host, Port, User, Password, and Service Name are correct

Solutions:

1. Confirm whether your Oracle environment requires wallet or certificate configuration.
2. Use an approved private connectivity path if your environment requires database connections to stay inside a private network.
3. Contact support if you need Oracle Wallet, TCPS, client certificate, or TNS descriptor configuration for this source.

---

Large table sync is slow or times out

Problem:

• A large table takes too long to read
• A sync fails with a query timeout or source resource error

Solutions:

1. Open Job Details and check the per-object failure to identify the table that timed out.
2. Use incremental sync for the failing table if it has a reliable cursor column.
3. Schedule the pipeline during off-peak hours when the Oracle database has more read capacity.
4. Review Oracle session and resource controls for the Supaflow user.
5. If the failing table is not identified, temporarily narrow the pipeline selection to isolate the object before tuning sync behavior.

Support

Need help? Contact us at support@supa-flow.io