Migrating Data from Oracle¶

This page covers Oracle-specific setup for Data migration. For workflow and Worker field definitions, see Data migration configuration reference.

Prerequisites¶

Oracle Instant Client and a matching ODBC driver on each Worker host.
Service name or TNS configuration for the target database.
For dbms_cloud extraction: a source user with EXECUTE on DBMS_CLOUD, a stored credential created with DBMS_CLOUD.CREATE_CREDENTIAL, and a network ACL that allows HTTPS to your object-store endpoint. See Server-side export with dbms_cloud.

Connectivity and extraction¶

Oracle supports two extraction strategies:

regular (default): partitioned SELECT through the Worker to Parquet, then the internal migration stage.
dbms_cloud: Oracle exports Parquet directly to object storage with DBMS_CLOUD.EXPORT_DATA, and Snowflake loads from an external stage. Data doesn’t pass through the Worker. See Server-side export with dbms_cloud.

Basic / EZConnect (default)¶

[connections.source.oracle]
oracle_connection_mode = "basic"
username = "scott"
password = "your_password"
database = "ORCL"
host = "db.example.com"
port = "1521"
# odbc_driver = "Oracle in instantclient_21_13"
# wallet_directory = "/path/to/wallet"
# wallet_password = "wallet_secret"

Connection modes¶

Mode	When to use	Key TOML fields
`basic`	Standard username/password (EZConnect)	`host`, `database` (service name), `port`
`tns_alias`	TNS name resolution	`tns_name`, optional `tns_admin`
`connect_descriptor`	Full DESCRIPTION block	Connect descriptor per site standards

Set odbc_driver to the exact name from pyodbc.drivers() on the Worker host, or use auto_detect_driver when appropriate.

Workflow example:

tables:
  - source:
      databaseName: HR
      schemaName: HR
      tableName: EMPLOYEES
    target:
      databaseName: TARGET_DB
      schemaName: HR
      tableName: EMPLOYEES
    columnNamesToPartitionBy:
      - EMPLOYEE_ID

Server-side export with `dbms_cloud`¶

With the dbms_cloud strategy, Oracle writes Parquet files directly to object storage using DBMS_CLOUD.EXPORT_DATA, and Snowflake loads them from an external stage. Because data never flows through the Worker, this suits large tables. It’s the Oracle counterpart to Redshift UNLOAD and Teradata WRITE_NOS.

Oracle prerequisites (a DBA typically handles these once):

Grant EXECUTE on DBMS_CLOUD to the source user.
Create a credential for your object store with DBMS_CLOUD.CREATE_CREDENTIAL. Credentials aren’t grantable, so the same user that runs the export must own it.
Add a network ACL that allows HTTPS to your object-store endpoint (for example *.amazonaws.com).

Worker TOML on the Oracle source connection (both keys are required; partial configuration is rejected):

[connections.source.oracle]
oracle_connection_mode = "basic"
username = "scott"
password = "your_password"
database = "ORCL"
host = "db.example.com"
port = "1521"

# Server-side export via DBMS_CLOUD.EXPORT_DATA
dbms_cloud_credential_name = "AWS_S3_CRED"
dbms_cloud_file_uri_prefix = "https://your-bucket.s3.us-west-2.amazonaws.com/your/prefix"
# dbms_cloud_format_clause = "JSON_OBJECT('type' VALUE 'parquet', 'compression' VALUE 'snappy')"  # optional; Parquet + snappy by default

dbms_cloud_file_uri_prefix must start with https://. The Worker appends the partition path and an export_ file-name prefix.
Export format defaults to Parquet with snappy compression. Only Parquet loads into Snowflake; don’t override dbms_cloud_format_clause with JSON.

Snowflake external stage must point at the same prefix Oracle writes to:

CREATE STAGE IF NOT EXISTS ORA_EXPORT_STAGE
  URL = 's3china://your-bucket/your/prefix/'
  STORAGE_INTEGRATION = STORAGE_ORACLE_DBMS;

Workflow example:

tables:
  - source:
      databaseName: HR
      schemaName: HR
      tableName: EMPLOYEES
    target:
      databaseName: TARGET_DB
      schemaName: HR
      tableName: EMPLOYEES
    columnNamesToPartitionBy:
      - EMPLOYEE_ID
    extraction:
      strategy: dbms_cloud
      externalStage: TARGET_DB.DATA_MIGRATION.ORA_EXPORT_STAGE

The workflow references the stage object, not the storage integration. Configuration parsing fails if externalStage is omitted.

Incremental sync¶

Oracle supports none and watermark. checksum is not supported for Oracle.

Data type mappings¶

Oracle type	Snowflake target type	Supported for migration	Notes
NUMBER, INTEGER, INT, SMALLINT	NUMBER	Yes
DECIMAL, NUMERIC, DEC	NUMBER	Yes
FLOAT, REAL, BINARY_FLOAT, BINARY_DOUBLE	FLOAT	Yes
DOUBLE PRECISION	FLOAT	Yes
VARCHAR2, NVARCHAR2, CHAR, NCHAR, VARCHAR	VARCHAR / CHAR	Yes	Oracle treats empty string as NULL
CLOB, NCLOB	TEXT	Yes	Very large LOBs may need workflow-level type overrides
LONG	VARCHAR	Yes
RAW, BLOB, LONG RAW	BINARY	Yes
BFILE	VARCHAR	Yes
DATE	TIMESTAMP_NTZ	Yes	Oracle DATE includes time; maps to TIMESTAMP_NTZ
TIMESTAMP	TIMESTAMP_NTZ	Yes	Nanoseconds truncated to microseconds
TIMESTAMP WITH TIME ZONE	TIMESTAMP_TZ	Yes
TIMESTAMP WITH LOCAL TIME ZONE	TIMESTAMP_LTZ	Yes	Converted using the session time zone
INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND	VARCHAR	Yes
ROWID, UROWID	VARCHAR	Yes
JSON, XMLTYPE	VARIANT	Yes
SDO_GEOMETRY	(unmapped)	No
BOOLEAN, VECTOR	(unmapped)	No

Platform-specific suggestions¶

Large tables: Prefer dbms_cloud so Oracle exports directly to object storage instead of streaming data through the Worker. Keep regular for smaller tables or when object-store access isn’t set up. See Server-side export with dbms_cloud.
Wallet-based TLS: Use wallet_directory and wallet_password in Worker TOML when your Oracle environment requires wallet authentication.
No checksum sync: Plan incremental loads with watermark instead of checksum, which is not supported for Oracle.
Anti-locking: AIM DMV adds an automatic PARALLEL optimizer hint on large Oracle tables. Override with queryModifiers.selectModifier, or set selectModifier to "NONE" to disable. See Anti-locking and query modifiers.