ORACLE

A COMPLETE PROFILES.YML

The most common setup — host, port, and the service name your listener publishes. Copy it, change five values, and you're connected:

# ~/.dbt/profiles.yml
my_project:
  target: oracle_dev
  outputs:
    oracle_dev:
      type: oracle
      host: oracle.internal.com
      port: 1521
      service: ORCLPDB1          # the service name, not a SID
      user: ANALYTICS
      password: "{{ env_var('ORA_PASSWORD') }}"
      schema: ANALYTICS          # optional — defaults to the user
      threads: 4

Then run dvt sync once from your project. It reads this profile, sees an Oracle output, and installs the adapter and the oracledbdriver for you. The driver runs in thin mode — no Oracle Instant Client, no OS packages, nothing to download from Oracle's site. Confirm the connection end to end with dvt debug.

CONNECTING WITH EZCONNECT

If you already have an EZConnect string — the host[:port]/service shorthand Oracle tools accept everywhere — put it in connection_string and skip the discrete fields entirely:

    oracle_dev:
      type: oracle
      connection_string: oracle.internal.com:1521/ORCLPDB1
      user: ANALYTICS
      password: "{{ env_var('ORA_PASSWORD') }}"

The port is optional inside the string (1521 is assumed), and connection_string wins over host/port/service when both are present. Behind the scenes DVT parses the string into its host, port, and service name before handing it to the data-movement engine — that engine rejects a bare EZConnect string, so DVT does the splitting for you rather than making you do it.

CONNECTING WITH A TNS DESCRIPTOR

For listeners that need the full connect descriptor — RAC, Data Guard, multi-address setups — paste the complete (DESCRIPTION=...) text as tns_name:

    oracle_prod:
      type: oracle
      tns_name: "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db1.internal.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PRODDB)))"
      user: ANALYTICS
      password: "{{ env_var('ORA_PASSWORD') }}"

A descriptor also works pasted straight into connection_string — anything starting with ( is treated as a descriptor in either field.

TLS LISTENERS — PROTOCOL: TCPS

If your listener speaks TLS (common on Autonomous Database and hardened on-prem setups), add one line:

    oracle_tls:
      type: oracle
      host: secure-db.internal.com
      port: 2484                 # TCPS listeners usually sit on 2484
      service: PRODDB
      protocol: tcps             # TLS to the listener
      user: ANALYTICS
      password: "{{ env_var('ORA_PASSWORD') }}"

protocol defaults to tcp, so you only ever write it for TLS. If a generated profile — including the ones DVT's own connection form writes — prefills protocol: tcp, that's harmless: it's just the default spelled out, and only tcps changes anything.

HOW ORACLE BEHAVES IN DVT

Schemas are users. In Oracle a schema and a user are the same object — ANALYTICS the user owns ANALYTICSthe schema. That's why schema is optional here and defaults to user: leave it out and your models build in the connecting user's own schema. Building into a differentschema means that user needs the matching grants, because you're creating objects in another user's territory.

Uppercase is the native case. Oracle folds unquoted identifiers to uppercase, so schema, user, and table names in the catalog are almost always uppercase. Write them that way in sources.yml — schema: FINANCE, name: GL_JOURNAL— and they'll match what the metadata queries return.

Service names, not SIDs.DVT always connects by service name. If you've seen ORA-12505from other tools against containers like XE's XEPDB1, that's the classic SID-vs-service confusion — service-name-only listeners have no SID to resolve. DVT sidesteps it by construction; just make sure the value you give it is the service the listener actually publishes (lsnrctl status shows the list).

Clean drops. When DVT drops a relation it does so in a PL/SQL block with CASCADE CONSTRAINTS PURGE — no orphaned foreign-key constraints blocking the drop, and nothing left behind in the recycle bin.

Bulk loads through SQL*Loader.Oracle's fast lane is its own client tool, sqlldr— with it, loads run through SQL*Loader's direct path; without it they fall back to batched inserts, which on a million-row seed is the difference between ~2 minutes and ~7. dvt sync provisions it (Instant Client tools, plus the client-library links macOS needs), and when it can't, warns with the exact install command rather than failing — a slow load is still a correct load.

Seeds keep their numeric types. Some CSV files pad their numbers with spaces ( 29.976 ), and Oracle's conversion lane historically typed such columns as text. DVT re-types a padded column back to a number only when every value in it genuinely is one — so your measures land as NUMBER ready for MIN/AVG, while columns that only look numeric (007 codes, 0.75% literals) keep their text type untouched. The same seed lands the same type class on Oracle as on every other engine.

Reading Oracle live from a federation model takes one meta.connection line pointing at the output you defined above:

# models/staging/sources.yml
sources:
  - name: erp
    meta:
      connection: oracle_dev   # a profiles.yml output name
    schema: FINANCE
    tables:
      - name: GL_JOURNAL
      - name: AP_PAYMENTS

Local development. The fastest way to an Oracle you can test against is a container:

docker run -d --name oracle-free -p 1521:1521 \
  -e ORACLE_PASSWORD=oracle gvenzl/oracle-free

# then in profiles.yml:
#   host: localhost, port: 1521, service: FREEPDB1
#   (older XE images publish XEPDB1 instead)

INCREMENTAL MODELS — THE DEFAULT IS MERGE, KEY OR NO KEY

The Oracle adapter accepts four strategy names, spelled exactly as they go into config: append, merge, delete+insert, and microbatch. insert_overwrite is not among them, and unlike the SQL Server family Oracle ships no helper macro for it at all.

Leave incremental_strategy unset and Oracle routes to merge unconditionally — it does not branch on the key the way SQL Server and Fabric do. The branch happens one level down instead: merge with a unique_key emits a real MERGE, and merge without one falls through to a plain INSERT. So an Oracle incremental with no strategy and no key is an append in everything but name.

{{ config(
    materialized='incremental',
    incremental_strategy='merge',   -- append | merge | delete+insert | microbatch
    unique_key='JOURNAL_ID',        -- without it, merge degrades to a plain insert
    parallel=4,                     -- adds /*+parallel(4)*/ to the emitted SQL
    insert_mode='append'            -- adds the /*+ append */ direct-path hint
) }}

select JOURNAL_ID, ACCOUNT, AMOUNT, UPDATED_AT
from {{ source('erp', 'GL_JOURNAL') }}
{% if is_incremental() %}
where UPDATED_AT > (select max(UPDATED_AT) from {{ this }})
{% endif %}

delete+insert has a second way to qualify. It needs a unique_key or incremental_predicates. With a key it deletes the matching keys and reinserts them; with predicates only, it deletes by predicate; with neither it emits a plain insert — no delete, and no error to tell you the delete never happened. The delete and the insert are wrapped in a BEGIN ... EXECUTE IMMEDIATE ... END; PL/SQL block, so both statements travel as one call.

merge is stricter than the generic version. Oracle excludes the unique-key columns from the UPDATE SET list, and it refuses to let you configure both column lists at once:

Model cannot specify merge_update_columns and merge_exclude_columns.
Please update model to use only one config

Identifier case reaches into the strategies.Key and event-time identifiers are quoted or not according to your model's column metadata, and anything left unquoted is upper-cased before it goes into the SQL — the same folding rule described above, applied to unique_key. Write your keys the way the catalog holds them and the two always agree.

microbatch is Oracle-shaped. It requires an event_time config, renders the batch boundaries as TO_TIMESTAMP literals, and emits its delete and insert inside a single BEGIN ... END; block. It ignores unique_key — it deletes by time predicate, then inserts.

What the other configs touch. parallel and insert_mode alter every insert-bearing strategy — append, merge without a key, delete+insert, and microbatch — by injecting /*+parallel(n)*/ and /*+ append */ hints; parallel also goes into the MERGEitself. Every strategy's SQL is wrapped in cleanup that truncates and drops the temp relation before re-raising if anything fails, and partition config is re-applied to the target before the strategy runs. Nothing is gated on an Oracle version, a table format, or a catalog type — the adapter declares no such conditions. Oracle's incremental materialization also accepts Python models, but only the temp-relation build differs; all four strategies operate on SQL either way.

A name the adapter does not accept is refused by dbt's own gate rather than by the adapter, and the message depends on the kind of name you wrote:

incremental_strategy='insert_overwrite'
  The incremental strategy 'insert_overwrite' is not valid for this adapter

incremental_strategy='upsert'
  dbt could not find an incremental strategy macro with the name
  "get_incremental_upsert_sql" in <your project>

When Oracle is the target of a federated model. An f_incremental model is computed by DVT and landed through its data-movement layer, so the vocabulary narrows to append, merge, and delete+insert; insert_overwrite and microbatch are refused as DVT025— "has no Sling equivalent — f_incremental supports append, merge and delete+insert". Oracle's PL/SQL wrappers, hints, and the incremental_predicates route belong to the native lane only. On the federated lane merge and delete+insert collapse into the same key-based upsert on unique_key; either one without a key warns and falls back to a full refresh instead of failing, and append without a watermark_column does the same. Oracle is one of the engines admitted to the keyless-watermark path — a federated incremental that leans on a watermark instead of a key is allowed here because DVT has verified this connector honouring an update-key-only load. Engines it has not verified — StarRocks, Snowflake, Databricks, BigQuery, Fabric and Athena — refuse the same model with DVT026. DVT rewrites no strategy on Oracle: the one substitution it makes anywhere is ClickHouse's merge to delete+insert.

REFERENCE — EVERY PROFILE FIELD

Everything an type: oracle output reads. Pick one way to say where the database is: connection_string, tns_name, or host/port/service — listed here in the order DVT checks them.