Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a distant database to replicate having huge data but I have access only to synonyms and I would like to use Oracle export/import tool to import these data into tables of local database.

What is the best solution to do this ?

To sum up:

  • Distant db :Oracle Database 10g Enterprise Edition Release - 64bit

    • Synoymns (through dblink) :
      • HUGE_TABLE : about 10M rows
      • HUGE_TABLE_WITH_BLOBS : about 1M rows, 10GB
  • Local db: Oracle Database 11g Enterprise Edition Release - 64bit

    • Tables:
      • HUGE_TABLE : empty (to fill)
      • HUGE_TABLE_WITH_BLOBS : empty (to fill)
share|improve this question
If you do an INSERT /*+APPEND*/ ( SELECT * FROM HUGE_TABLE@otherdb) over the DB link you won't need an intermediate step of dumping to disk. I can't remember off-hand how well CLOB/BLOB fields play over DB links – Phil Mar 5 '14 at 13:13

I think the most efficient way in your case will be:

--uncomment if you are allowed do this: drop table HUGE_TABLE;
--uncomment if you are allowed do this: drop table HUGE_TABLE_WITH_BLOBS;
create table HUGE_TABLE NOLOGGING PARALLEL 4 as select * from HUGE_TABLE@Distantdb;

You can omit: "NOLOGGING PARALLEL 4" if normal mode will be enough fast. Remember about creating constraint and indexes on newly created tables, default only NOT NULL constraints will be copied.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.