2

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 10.2.0.4.0 - 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 11.2.0.1.0 - 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ᵀᴹ Mar 5 '14 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 Mar 20 '18 at 11:26
2

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;
create table HUGE_TABLE_WITH_BLOBS NOLOGGING PARALLEL 4 as select * from HUGE_TABLE_WITH_BLOBS@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, privacy policy and cookie policy

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