Oracle Examples

Comprehensive examples for transferring data from and to Oracle databases.

Connection Types

Oracle transfers support multiple connection types:

• oraodp: Native Oracle Data Provider (recommended for source)
• orabulk: Oracle Bulk Loader for high-performance target loading
• oradirect: Oracle Direct Path for target loading
• odbc: Using ODBC DSN

Oracle to MSSQL - Single-threaded Transfer

Simple transfer without parallelization.

.\FastTransfer.exe `
  --sourceconnectiontype "oraodp" `
  --sourceserver "localhost:1521/ORCLPDB" `
  --sourcedatabase "ORCLPDB" `
  --query "select * from TPCH_IN.ORDERS FETCH NEXT 10 ROWS ONLY" `
  --sourceuser "TPCH_IN" `
  --sourcepassword "TPCH_IN" `
  --sourceschema "TPCH_IN" `
  --sourcetable "ORDERS" `
  --targetconnectiontype "msbulk" `
  --targetserver "localhost" `
  --targetdatabase "tpch_test" `
  --targettrusted `
  --targetschema "dbo" `
  --targettable "orders" `
  --parallelmethod "None" `
  --loadmode "Append"

Oracle to MSSQL - Parallel Transfer with Rowid Method

Oracle-specific parallel transfer using physical row identifier (ROWID).

Oracle-Specific

The Rowid method is unique to Oracle and uses the physical row identifier for parallel data distribution. This is very efficient for full table transfers.

.\FastTransfer.exe `
  --sourceconnectiontype "oraodp" `
  --sourceserver "LOCALHOST:1521/ORCLPDB" `
  --sourcedatabase "ORCLPDB" `
  --sourceuser "TPCH_IN" `
  --sourcepassword "TPCH_IN" `
  --sourceschema "TPCH_IN" `
  --sourcetable "ORDERS_FLAT" `
  --targetconnectiontype "msbulk" `
  --targetserver "localhost" `
  --targetdatabase "tpch_test" `
  --targettrusted `
  --targetschema "dbo" `
  --targettable "orders" `
  --parallelmethod "Rowid" `
  --loadmode "Truncate" `
  --degree 12

PostgreSQL to Oracle - Using orabulk (Bulk Loader)

Transfer data from PostgreSQL to Oracle using Oracle bulk loader.

Without parallelization:

.\FastTransfer.exe `
  --sourceconnectiontype "pgsql" `
  --sourceserver "localhost:5432" `
  --sourcedatabase "tpch" `
  --sourceuser "FastUser" `
  --sourcepassword "FastPassword" `
  --targetconnectiontype "orabulk" `
  --targetserver "localhost:1521/ORCLPDB" `
  --targetdatabase "ORCLPDB" `
  --query "select * from tpch_10.orders" `
  --targetuser "TPCH_IN" `
  --targetpassword "TPCH_IN" `
  --targetschema "TPCH_IN" `
  --targettable "ORDERS" `
  --parallelmethod "None" `
  --loadmode "Truncate" `
  --batchsize 100000

With Ctid parallelization:

.\FastTransfer.exe `
  --sourceconnectiontype "pgsql" `
  --sourceserver "localhost:5432" `
  --sourcedatabase "tpch" `
  --sourceuser "FastUser" `
  --sourcepassword "FastPassword" `
  --targetconnectiontype "orabulk" `
  --targetserver "localhost:1521/ORCLPDB" `
  --targetdatabase "ORCLPDB" `
  --sourceschema "tpch_10" `
  --sourcetable "orders" `
  --targetuser "TPCH_IN" `
  --targetpassword "TPCH_IN" `
  --targetschema "TPCH_IN" `
  --targettable "ORDERS" `
  --parallelmethod "Ctid" `
  --degree 12 `
  --loadmode "Truncate" `
  --batchsize 100000

PostgreSQL to Oracle - Using oradirect (Direct Path)

Transfer data from PostgreSQL to Oracle using Oracle direct path loading.

Without parallelization:

.\FastTransfer.exe `
  --sourceconnectiontype "pgsql" `
  --sourceserver "localhost:5432" `
  --sourcedatabase "tpch" `
  --sourceuser "FastUser" `
  --sourcepassword "FastPassword" `
  --targetconnectiontype "oradirect" `
  --targetserver "localhost:1521/ORCLPDB" `
  --targetdatabase "ORCLPDB" `
  --query "select * from tpch_10.orders limit 10005" `
  --targetuser "TPCH_IN" `
  --targetpassword "TPCH_IN" `
  --targetschema "TPCH_IN" `
  --targettable "ORDERS" `
  --parallelmethod "None" `
  --loadmode "Append" `
  --batchsize 1000

With Ctid parallelization:

.\FastTransfer.exe `
  --sourceconnectiontype "pgsql" `
  --sourceserver "localhost:5432" `
  --sourcedatabase "tpch" `
  --sourceuser "FastUser" `
  --sourcepassword "FastPassword" `
  --targetconnectiontype "oradirect" `
  --targetserver "localhost:1521/ORCLPDB" `
  --targetdatabase "ORCLPDB" `
  --sourceschema "tpch_10" `
  --sourcetable "orders" `
  --targetuser "TPCH_IN" `
  --targetpassword "TPCH_IN" `
  --targetschema "TPCH_IN" `
  --targettable "ORDERS" `
  --parallelmethod "Ctid" `
  --degree 10 `
  --loadmode "Truncate" `
  --batchsize 10000

With DataDriven method:

.\FastTransfer.exe `
  --sourceconnectiontype "pgsql" `
  --sourceserver "localhost:5432" `
  --sourcedatabase "tpch" `
  --sourceuser "FastUser" `
  --sourcepassword "FastPassword" `
  --targetconnectiontype "oradirect" `
  --targetserver "localhost:1521/ORCLPDB" `
  --targetdatabase "ORCLPDB" `
  --query "select * from tpch_10.orders" `
  --targetuser "TPCH_IN" `
  --targetpassword "TPCH_IN" `
  --targetschema "TPCH_IN" `
  --targettable "ORDERS" `
  --parallelmethod "DataDriven" `
  --distributeKeyColumn "(EXTRACT('Year' FROM o_orderdate))" `
  --degree 7 `
  --loadmode "Truncate" `
  --batchsize 10000

MySQL to Oracle - Using oradirect

Transfer data from MySQL to Oracle using direct path loading.

.\FastTransfer.exe `
  --sourceconnectiontype "mysql" `
  --sourceserver "localhost" `
  --sourcedatabase "tpch" `
  --sourceuser "FastUser" `
  --sourcepassword "FastPassword" `
  --targetconnectiontype "oradirect" `
  --targetserver "localhost:1521/ORCLPDB" `
  --targetdatabase "FREEPDB1" `
  --query "select * from tpch.orders_part" `
  --targetuser "TPCH_IN" `
  --targetpassword "TPCH_IN" `
  --targetschema "TPCH_IN" `
  --targettable "ORDERS_PART" `
  --parallelmethod "DataDriven" `
  --distributeKeyColumn "o_orderdate_year" `
  --degree 10 `
  --batchsize 10000 `
  --loadmode Truncate

Using ODBC Connection to Oracle

Transfer using ODBC DSN for Oracle connection.

To MSSQL - single-threaded:

.\FastTransfer.exe `
  --sourceconnectiontype "odbc" `
  --sourcedsn "DSNORA23CFREEPDB1" `
  --sourcedatabase "FREEPDB1" `
  --query "select * from TPCH_IN.ORDERS FETCH NEXT 10 ROWS ONLY" `
  --sourceuser "TPCH_IN" `
  --sourcepassword "TPCH_IN" `
  --sourceschema "TPCH_IN" `
  --sourcetable "ORDERS" `
  --targetconnectiontype "msbulk" `
  --targetserver "localhost" `
  --targetdatabase "tpch_test" `
  --targettrusted `
  --targetschema "dbo" `
  --targettable "orders" `
  --parallelmethod "None" `
  --loadmode "Truncate"

tip

Build your command with the Wizard