By
Published: 14 May 2008
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.
Dig Deeper on Oracle database administration
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs.
Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command.
Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming.
Continue Reading