Using Oracle External
Tables for Data transfer
Arup Nanda - OTN
Oracle9i Database introduced
external tables, which allow a formatted plain text file to be
visible to the database as a table that can be selected by
regular SQL. Suppose you have to move the contents of the table
named TRANS from the OLTP database to the data warehouse
database using this external table approach. Here are the steps
to accomplish that.
The most time-consuming step here is Step
1, in which the plain text file is created. You could create
this file using plain SQL and spooling to a file—a simple yet
lengthy process. You can make the process somewhat faster by
using a Pro*C or OCI program instead of SQL*Plus to offload the
records to a flat file, but it will still take a while. The
other "speed bump" is the need to specify the columns
manually—another time-consuming process.
- From the OLTP database, create a plain text file with
the contents of the table TRANS. The file can be called
trans_flat.txt in the directory /home/oracle/dump_dir.
Usually this file is created with this SQL:
select <column_1> ||','|| <column_2> ||','|| ...
- Copy the file over to the data warehouse server using
ftp, rcp, or some other mechanism. The file exists in the
- On the data warehouse database, create a directory
object named dump_dir as:
create directory dump_dir as '/home/oracle/dump_dir';
- Create an external table:
create table trans_ext
... <columns of the table> ...
default directory admin
records delimited by newline
fields terminated by "," optionally enclosed by '"'
... <columns> ...
reject limit unlimited;
- Now load the external table into the regular tables
using any common method such as direct load insert and