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 [email protected]_db_link;
Many times, this performs faster than export/import.
