Export synonyms content to tables

blobexportoracleoracle-10greplication

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)

Best Answer

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.

Related Question