Q

How to move data faster with a database link

Oracle expert Brian Peasland explains how to move data across a database link to speed up an import.

I am moving a database from HP to Solaris 10. I have a table that has only 240,000 rows, but there is a CLOB field with an XML doc. I was wondering if there was a quicker way to import the data. When doing a full import it takes almost a day and a half. The majority of this is only that table. Is there another way to speed up the import of this table? We are only allowed a 3 hour downtime window and the current exp/imp will not allow that.
You might want to consider moving the data across a database link. On your Solaris server, modify the tnsnames.ora configuration file to contain an entry which points to the HP server (I'll call this entry "hp_db" in my example below). Now that you have defined the network information to find the HP database, create a database link in the Solaris database:

CREATE DATABASE LINK hp_db_link AS CONNECT TO scott IDENTIFIED BY tiger USING 'hp_db';

You will need a valid username and password instead of scott/tiger. Now that the db link has been created, precreate the table in the Solaris db. Then copy the data from the HP db across the link and insert into the Solaris table similar to the following:

INSERT INTO solaris_table SELECT * FROM hp_table@hp_db_link;

Many times, this performs faster than export/import.

This was first published in May 2008

Dig deeper on Oracle database administration

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close