Take the 2-minute tour ×
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. It's 100% free, no registration required.

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)
share|improve this question
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 at 13:13
add comment

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;
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.

share|improve this answer
add comment

Your Answer

 
discard

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.