Import data via a network link in Oracle 10g

by Bob Watkins | Nov 07, 2006 8:00:00 AM

Tags: Storage, Databases, Oracle Application Server 10g, network, Bob Watkins, database, Oracle Corp., Oracle Tips Newsletter

Takeaway: Oracle 10g's import utility can use a network connection to directly import data from another Oracle database. Bob Watkins explains how it's done.

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
   USING 'CHI';

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 Seattle, user ADMIN2 executes the following command (all one line):

impdp admin2/market TABLES=customers,sales DIRECTORY=dpump1
  NETWORK_LINK=chicago

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!

Bob Watkins (OCP, MCDBA, MCSE, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. Visit Bob's site.