Database Reference
In-Depth Information
Chapter 15
Data Loading and Unloading
In this chapter, we will discuss data loading and unloading—in other words, how to get data
into
and
out of
an Oracle
database. The main focus of the chapter is on the following bulk data loading tools:
•
External tables
: This is a feature with Oracle9
i
and above that permits access to operating
system files as if they were database tables and, in Oracle 10
g
and above, even allows for the
creation of operating system files as extracts of tables.
•
SQL*Loader (pronounced “sequel loader” and hereafter referred to as SQLLDR)
: This is Oracle's
historical data loader that is still a common method for loading data.
In the area of data unloading, we'll look at the following techniques:
•
Data Pump unload
: Data Pump is a binary format proprietary to Oracle and accessible via the
Data Pump tool and external tables.
•
Flat file unload
: The flat file unloads will be custom developed implementations, but provide
you with a result that is portable to other types of systems (even a spreadsheet).
External Tables
External tables were first introduced in Oracle9
i
Release 1. Put simply, they allow us to treat an operating system file as
if it is a read-only database table. They are not intended to be a replacement for a “real” table, or to be used in place of
a real table; rather, they are intended to be used as a tool to ease the loading and, in Oracle 10
g
and above, unloading
of data.
When the external tables feature was first unveiled, I often referred to it as “the replacement for SQLLDR.” This
idea still holds true—
most
of the time. Having said this, you might wonder why there is material in this chapter that
covers SQLLDR. The reason is that SQLLDR has been around for a long time, and there are many, many legacy control
files lying around. SQLLDR is still a commonly used tool; it is what many people know and have used. We are still in a
period of transition from the use of SQLLDR to external tables, thus SQLLDR is still very relevant.
What many DBAs don't realize is that their knowledge of SQLLDR control files is readily transferable to the use
of external tables. You'll discover, as we work through the examples in this part of the chapter, that external tables
incorporate much of the SQLLDR syntax and many of the techniques.
Having said that, SQLLDR should be chosen
over
external tables in the following situations:
•
You have to load data over a network—in other words, when the input file is not on the
database server itself. One of the restrictions of external tables is that the input file must be
accessible on the database server.
•
Multiple users must
concurrently
work with the
same
external table processing different
input files.
