Skip to main content
Version: 1.7 (Latest)

Oracle Examples

Comprehensive examples for exporting data from Oracle databases.

Connection Types​

Oracle exports support multiple connection types:

  • oraodp: Native Oracle Data Provider (recommended)
  • oledb: Using Oracle OLEDB Provider (OraOLEDB)
  • odbc: Using ODBC DSN
  • adbc_oracle: ADBC FastDriver — ultra-fast, Parquet-only export (see ADBC Examples)
  • backup_rman: tables read straight from RMAN backup sets, full and incremental, without Oracle and without a restore — Parquet-only (see Oracle RMAN Backup Sets)
ADBC Support

The adbc_oracle connection type uses the Arpe.io ADBC FastDriver for ultra-fast, Arrow-native Parquet exports, and also supports the Physical parallel method (resolved to Rowid). Output is Parquet-only — see the ADBC Examples section below.

Oracle syntax in --query

Since FastBCP 1.7.0, a --query on Oracle (oraodp, adbc_oracle, or odbc when the driver reports an Oracle server) is read with Oracle's own SQL rules:

  • # is part of a name, so SELECT SID, SERIAL# FROM V$SESSION keeps its SERIAL# column (up to 1.6 the end of the line was dropped as a comment);
  • a backslash is an ordinary character: 'C:\temp\' parses, and LIKE 'C:\temp\f1%' matches what Oracle matches (up to 1.6, \t became a tab and the filter could silently return no rows);
  • the (+) outer join, IGNORE NULLS in window functions and database links (T@DBLINK) keep working.

Single-threaded Export​

Simple export without parallelization.

.\FastBCP.exe `
--connectiontype "oraodp" `
--server "localhost:1521/ORCLPDB1" `
--user "system" `
--password "password" `
--query "SELECT * FROM customers WHERE ROWNUM <= 1000" `
--directory "C:\temp" `
--fileoutput "oracle_customers.csv" `
--decimalseparator "." `
--delimiter "|" `
--dateformat "yyyy-MM-dd HH:mm:ss" `
--encoding "UTF-8" `
--parallelmethod "None"

Parallel Export - Physical Method​

Parallel export using the physical row identifier (ROWID).

Physical on Oracle

With an Oracle connection type (oraodp, adbc_oracle), the Physical method resolves to the Oracle ROWID strategy (formerly --parallelmethod Rowid, now deprecated). It is very efficient for full table exports and does not require a distribute key column. --sourceschema and --sourcetable are required.

Full table export:

.\FastBCP.exe `
--connectiontype "oraodp" `
--server "localhost:1521/ORCLPDB1" `
--user "system" `
--password "password" `
--sourceschema "SYSTEM" `
--sourcetable "ORDERS" `
--directory "C:\temp" `
--fileoutput "oracle_orders.csv" `
--decimalseparator "." `
--delimiter "|" `
--dateformat "yyyy-MM-dd HH:mm:ss" `
--encoding "UTF-8" `
--parallelmethod "Physical" `
--paralleldegree 8 `
--merge "False" `
--runid "oraodp_to_csv_parallel8_physical"

With query filter:

.\FastBCP.exe `
--connectiontype "oraodp" `
--server "localhost:1521/ORCLPDB1" `
--user "system" `
--password "password" `
--sourceschema "SYSTEM" `
--sourcetable "ORDERS" `
--query "SELECT * FROM SYSTEM.ORDERS WHERE order_date >= TO_DATE('2024-01-01', 'YYYY-MM-DD')" `
--directory "C:\temp" `
--fileoutput "oracle_orders_2024.csv" `
--decimalseparator "." `
--delimiter "|" `
--dateformat "yyyy-MM-dd HH:mm:ss" `
--encoding "UTF-8" `
--parallelmethod "Physical" `
--paralleldegree 8 `
--merge "False" `
--runid "oraodp_to_csv_parallel8_physical_filtered"

Parallel Export - Random Method​

Export using Random distribution with modulo operation.

.\FastBCP.exe `
--connectiontype "oraodp" `
--server "localhost:1521/ORCLPDB1" `
--user "system" `
--password "password" `
--sourceschema "SYSTEM" `
--sourcetable "ORDERS" `
--directory "C:\temp" `
--fileoutput "oracle_orders_random.csv" `
--decimalseparator "." `
--delimiter "|" `
--dateformat "yyyy-MM-dd HH:mm:ss" `
--encoding "UTF-8" `
--parallelmethod "Random" `
--distributekeycolumn "order_id" `
--paralleldegree 8 `
--merge "False" `
--runid "oraodp_to_csv_parallel8_random"

Parallel Export - DataDriven Method​

Export using DataDriven distribution based on distinct column values.

.\FastBCP.exe `
--connectiontype "oraodp" `
--server "localhost:1521/ORCLPDB1" `
--user "system" `
--password "password" `
--sourceschema "SYSTEM" `
--sourcetable "ORDERS" `
--directory "C:\temp" `
--fileoutput "oracle_orders_dd.csv" `
--decimalseparator "." `
--delimiter "|" `
--dateformat "yyyy-MM-dd HH:mm:ss" `
--encoding "UTF-8" `
--parallelmethod "DataDriven" `
--distributekeycolumn "order_status" `
--paralleldegree 10 `
--merge "False" `
--runid "oraodp_to_csv_parallel10_datadriven"

Export to Parquet​

Export Oracle data to Parquet format.

Single-threaded:

.\FastBCP.exe `
--connectiontype "oraodp" `
--server "localhost:1521/ORCLPDB1" `
--user "system" `
--password "password" `
--sourceschema "SYSTEM" `
--sourcetable "ORDERS" `
--directory "C:\temp" `
--fileoutput "oracle_orders.parquet" `
--parallelmethod "None"

Parallel with Physical (Rowid):

.\FastBCP.exe `
--connectiontype "oraodp" `
--server "localhost:1521/ORCLPDB1" `
--user "system" `
--password "password" `
--sourceschema "SYSTEM" `
--sourcetable "ORDERS" `
--directory "C:\temp" `
--fileoutput "oracle_orders.parquet" `
--parallelmethod "Physical" `
--paralleldegree 10 `
--merge "False"

OLEDB Connection Examples​

Using Oracle OLEDB Provider for connections.

Single-threaded:

.\FastBCP.exe `
--connectiontype "oledb" `
--provider "OraOLEDB.Oracle" `
--server "localhost:1521/ORCLPDB1" `
--user "system" `
--password "password" `
--query "SELECT * FROM SYSTEM.ORDERS WHERE ROWNUM <= 10000" `
--directory "C:\temp" `
--fileoutput "oracle_orders.csv" `
--decimalseparator "." `
--delimiter "|" `
--dateformat "yyyy-MM-dd HH:mm:ss" `
--encoding "UTF-8" `
--parallelmethod "None"

Parallel Random:

.\FastBCP.exe `
--connectiontype "oledb" `
--provider "OraOLEDB.Oracle" `
--server "localhost:1521/ORCLPDB1" `
--user "system" `
--password "password" `
--query "SELECT * FROM SYSTEM.ORDERS" `
--directory "C:\temp" `
--fileoutput "oracle_orders.csv" `
--decimalseparator "." `
--delimiter "|" `
--dateformat "yyyy-MM-dd HH:mm:ss" `
--encoding "UTF-8" `
--parallelmethod "Random" `
--distributekeycolumn "order_id" `
--paralleldegree 6 `
--merge "False"

Parallel DataDriven:

.\FastBCP.exe `
--connectiontype "oledb" `
--provider "OraOLEDB.Oracle" `
--server "localhost:1521/ORCLPDB1" `
--user "system" `
--password "password" `
--query "SELECT * FROM SYSTEM.ORDERS" `
--directory "C:\temp" `
--fileoutput "oracle_orders.csv" `
--decimalseparator "." `
--delimiter "|" `
--dateformat "yyyy-MM-dd HH:mm:ss" `
--encoding "UTF-8" `
--parallelmethod "DataDriven" `
--distributekeycolumn "order_status" `
--paralleldegree 6 `
--merge "False"

ADBC Examples​

The adbc_oracle connection type uses the Arpe.io ADBC FastDriver for ultra-fast, Arrow-native exports. Output is Parquet-only, and the Physical parallel method (resolved to Rowid) is also supported — see Connection Parameters and Parallel Parameters for details.

./FastBCP \
--connectiontype "adbc_oracle" \
--server "localhost:1521/orclpdb1" \
--user "MIG_EXPRESS" \
--password "FastPassword" \
--sourceschema "TPCH10" \
--sourcetable "LINEITEM" \
--directory "./" \
--fileoutput "{sourcetable}.parquet" \
--method "None"

Oracle RMAN Backup Sets (backup_rman)​

The backup_rman connection type reads a table straight from RMAN backup sets, full and incremental, without restoring them and without an Oracle instance: on a backup server, from an archive, or for a database that is gone. --server is the directory of the backup pieces and --database their names or patterns. Output is Parquet-only — see Oracle RMAN backup sets for what is supported.

A full backup, parallel export (Physical resolves to Pages):

.\FastBCP.exe `
--connectiontype "backup_rman" `
--server "D:\Backups\RMAN\ORCL" `
--database "full_*" `
--sourceschema "TPCH10" `
--sourcetable "LINEITEM" `
--directory "D:\export\{sourcetable}" `
--fileoutput "{sourcetable}.parquet" `
--parallelmethod "Physical" `
--paralleldegree 8

Pieces of several backup sets (several channels, FILESPERSET, MAXPIECESIZE, or a separate control file backup): every piece, by patterns separated by ;, in any order. A compressed backup (AS COMPRESSED BACKUPSET, BASIC, LOW, MEDIUM or HIGH) is read the same way:

./FastBCP \
--connectiontype "backup_rman" \
--server "/backup/rman/orcl" \
--database "db_*;ctl_*" \
--sourceschema "SALES" \
--sourcetable "ORDERS" \
--directory "/export/{sourcetable}" \
--fileoutput "{sourcetable}.parquet" \
--parallelmethod "Physical" \
--paralleldegree 8

A table of another PDB, a few columns (CONTAINER.OWNER.TABLE in --query; the columns of types that cannot be read, such as a LOB, are simply left out):

./FastBCP \
--connectiontype "backup_rman" \
--server "/backup/rman/cdb1" \
--database "full_*" \
--query "SELECT EMPLOYEE_ID, LAST_NAME, HIRE_DATE FROM HRPDB.HR.EMPLOYEES" \
--directory "/export/employees" \
--fileoutput "employees.parquet" \
--parallelmethod "None"

A level 0 and its incremental backups (since FastBCP 1.6.8): name the whole chain, in any order. The latest version of the table is read, the whole table. Here the level 0 is in l0 and the level 1 backups in a directory per day below l1:

.\FastBCP.exe `
--connectiontype "backup_rman" `
--server "D:\Backups\RMAN\ORCL" `
--database "l0\*;l1\**" `
--sourceschema "SALES" `
--sourcetable "ORDERS" `
--directory "D:\export\{sourcetable}" `
--fileoutput "{sourcetable}.parquet" `
--parallelmethod "Physical" `
--paralleldegree 8

Every backup below the directory, whatever its level (**): older backups can match too, only the latest version is read:

./FastBCP \
--connectiontype "backup_rman" \
--server "/backup/rman/orcl" \
--database "**" \
--sourceschema "SALES" \
--sourcetable "ORDERS" \
--directory "/export/{sourcetable}" \
--fileoutput "{sourcetable}.parquet" \
--parallelmethod "Physical" \
--paralleldegree 8

The level 0 only, the incremental backups ignored, with FASTBCP_BACKUP_RESTORE_POINT:

export FASTBCP_BACKUP_RESTORE_POINT=full
./FastBCP \
--connectiontype "backup_rman" \
--server "/backup/rman/orcl" \
--database "l0/*;l1/**" \
--sourceschema "SALES" \
--sourcetable "ORDERS" \
--directory "/export/{sourcetable}" \
--fileoutput "{sourcetable}.parquet" \
--parallelmethod "None"

See Incremental backups for what is read, Naming the backup pieces for the patterns, and Backups that cannot be used for when an export fails.

note

An incremental backup without its level 0, a backup set without one of its pieces and an encrypted backup set (SET ENCRYPTION) fail the export, naming the backup. Image copies are not read.

tip

Build your command with the Wizard

Copyright © 2026 Architecture & Performance.