Data Copy Utilities

This chapter focuses on utilities that copy data. Data can be copied in many different ways.

First, data can be exported from within Oracle to outside of Oracle (export). Second, data can be imported from outside of the database to inside the database (SQL*Loader). Data can also be copied from one database to another (export/import or SQL*Plus copy command) as depicted in Figure 4.1.

Choosing the appropriate method for copying data from a source to a destination requires knowledge of the strengths and weaknesses of each data copy utility. Depending on the job at hand, one method may be more suitable than another. This chapter will explain each of the data copy utilities to help make the choice easier.

Throughout this chapter, certain benchmark tests are used. These tests all include the same table, the table with one million rows.

SQL> desc table_with_one_million_rows;

Name Null? Type
------------------------------------- -------- ------------
COL1 NUMBER
COL2 NUMBER
COL3 VARCHAR2(80)
COL4 VARCHAR2(80)
COL5 VARCHAR2(80)
COL6 DATE

The tests were conducted in a single environment running Oracle 9.2 and Windows XP, with an 8K Oracle blocksize. The same tests should be performed in your environment in order to confirm the results. Environmental attributes, such as data types, I/O layout, block sizes, file systems, etc., can impact the results.