Oracle's export and import utilities have historically used a disk file as intermediate storage when unloading or reloading the database. For large databases, this "dump file" was an issue because operating system limits on file size could be exceeded, making export impossible.
Creative DBAs have used file compression utilities, such as compress on UNIX, to get the most capacity from the dump file. Later versions of import and export allowed the use of multiple dump files to get around the limits.
In Oracle 10g, the Data Pump version of import can eliminate the dump file entirely by importing directly from another database instance.
The first step is to define a database link object to identify the source database and provide login credentials. For example, a source database in Chicago might be identified by the Oracle network service name CHI. A user in that instance, ADMIN1, logs in using the password WINDY and has the correct privileges to access the data to be imported. The following CREATE DATABASE LINK command, then, could be used to define the source database:
CREATE DATABASE LINK chicago
CONNECT TO admin1 IDENTIFIED BY windy
The Data Pump import command, impdp,
can now use this database link to directly access remote data. The command line
parameter NETWORK_LINK points to the source database via its database link. On
the local database instance in
impdp admin2/market TABLES=customers,sales DIRECTORY=dpump1
Note that no export operation is performed first on the instance in Chicago, and no dump file is created during the process.
For more information on imports via a network link, consult the Oracle Database 10g Utilities book in the Oracle documentation set.
Miss a tip?
Check out the Oracle archive, and catch up on our most recent Oracle tips.
TechRepublic's free Oracle newsletter, delivered each Wednesday, covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more. Automatically subscribe today!