How to Bulk Load Data in Oracle

How do I bulk load large CSV files into Oracle quickly?

Bulk loading imports large files into Oracle tables at high speed using tools like SQL*Loader and External Tables.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why pick bulk load over plain INSERT?

Bulk loading bypasses row-by-row processing, letting Oracle write data in large blocks. Direct-path mode can be 10–20× faster than INSERT, avoids undo overhead, and minimizes logging.

Which Oracle tools support bulk loads?

SQL*Loader, External Tables, and Data Pump Import (impdp) all move data quickly. SQL*Loader gives the most control and works from flat files.External Tables expose files as read-only tables, perfect for one-off loads or ETL staging.

How do I prepare my ecommerce tables?

Disable unnecessary indexes and foreign keys on Customers, Orders, Products, and OrderItems. Make sure target columns match the file layout. Create staging tables if you need data cleansing first.

What is the SQL*Loader command syntax?

Run sqlldr from the OS shell, point to a control file, and choose conventional or direct path.The control file maps file fields to table columns and sets load options.

Sample control file questions

Q: How do I load a CSV into Customers?
Q: How do I skip the header row?
Q: How do I maintain sequence values?

How do I create the control file?

Write a plain text file ending in .ctl. List the target table, file location, delimiters, and column mappings.Use FIELDS TERMINATED BY ',' for CSV and OPTIONALLY ENCLOSED BY '"' for quoted strings.

How do I launch a direct-path load?

Use DIRECT=TRUE in the SQL*Loader command. This lets Oracle write blocks directly to datafiles, ignoring the buffer cache, so it is much faster for millions of rows.

How can I monitor and verify the load?

SQL*Loader produces a log file, bad file (rejected rows), and discard file (filtered rows). Check the log for elapsed time, logical records read, and any ORA-errors.Validate counts against the source file.

Best practices for production loads?

Load into staging tables, validate, then INSERT /*+ APPEND */ into production. Parallelize loads with PARALLEL=TRUE. Always collect statistics after loading to keep the optimizer accurate.

What common mistakes should I avoid?

Do not forget to set SKIP=1 when files have headers. Avoid loading into tables with active triggers—they fire for every row and slow the job.

Why How to Bulk Load Data in Oracle is important

How to Bulk Load Data in Oracle Example Usage


-- Load orders with SQL*Loader
echo "Running bulk load for Orders" && \
sqlldr userid="user/password@XE" control=orders.ctl log=orders.log data=orders.csv DIRECT=TRUE

-- orders.ctl
LOAD DATA
INFILE 'orders.csv'
APPEND INTO TABLE Orders
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(order_id    INTEGER EXTERNAL,
 customer_id INTEGER EXTERNAL,
 order_date  DATE "YYYY-MM-DD",
 total_amount DECIMAL EXTERNAL)

-- Verify after load
SELECT COUNT(*) FROM Orders;
SELECT SUM(total_amount) FROM Orders;

How to Bulk Load Data in Oracle Syntax


-- SQL*Loader command
sqlldr userid="user/password@XE" control=customers.ctl log=customers.log bad=customers.bad \
       data=customers.csv DIRECT=TRUE PARALLEL=TRUE

-- customers.ctl (control file)
LOAD DATA
INFILE 'customers.csv'
BADFILE 'customers.bad'
APPEND INTO TABLE Customers
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  id             INTEGER EXTERNAL,
  name           CHAR,
  email          CHAR,
  created_at     DATE  "YYYY-MM-DD HH24:MI:SS"
)

-- External table alternative
CREATE TABLE ext_customers
( id number,
  name varchar2(100),
  email varchar2(150),
  created_at date
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
  DEFAULT DIRECTORY data_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    ( id, name, email, created_at DATE "YYYY-MM-DD HH24:MI:SS" )
  )
  LOCATION ('customers.csv')
);

-- Insert from external table
INSERT /*+ APPEND */ INTO Customers SELECT * FROM ext_customers;

Common Mistakes

Loading while indexes and triggers are active slows inserts dramatically. Disable or drop them before the load, then recreate afterward.
Forgetting DIRECT=TRUE on big files forces conventional path and multiplies runtime. Always enable DIRECT for multi-million-row loads.

Frequently Asked Questions (FAQs)

Can I load multiple tables in one run?

Yes. List each table in the same control file, separated by INTO TABLE clauses. SQL*Loader processes them sequentially from the same data file or different files.

How do I handle bad rows?

SQL*Loader writes rejected records to the .bad file. Inspect, fix, and re-load them with sqlldr ... data=customers.bad. The WHEN clause in the control file can pre-filter rows.

Is External Tables slower than SQL*Loader?

Performance is similar for single-thread loads. SQL*Loader direct path edges ahead at high parallelism, but External Tables integrate cleanly with SQL and need no separate logs.