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.

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: