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)
  • 1
    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ᵀᴹ
    Commented Mar 5, 2014 at 13:13
  • It looks like exp utility only operates on real tables, for synonyms I got: EXP-00011: APP.AGENCY does not exist
    – gavenkoa
    Commented Mar 20, 2018 at 11:26

1 Answer 1


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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