0

I have a remote table with 300 millions of records, I need to insert all the data in a table in my local database.

I try using

All the options take too much time.

What other options can I use to insert all the records in an efficient way?

I am using Oracle 11g in the remote and local database

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Joseleg
  • 393
  • 9
  • 35
  • well in my scenario i took dump of the table to local db then I insert them with a cursor loop with 2 column conditions , the good with the cursor with right conidtions will use bind variable method, ofcourse index was there too – Moudiz Aug 02 '18 at 15:36
  • 2
    Have you looked at data pump (export/import, optionally over a DB link)? – Alex Poole Aug 02 '18 at 15:48
  • 1
    Transportable tablespace? – David Aldridge Aug 02 '18 at 16:06

1 Answers1

0

Another option is taking dump of the table and load it locally then you insert the data. Normally I use a cursor loop with bind variable over bulk insert. However bulk collect is interesting you can use it.

expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log
Moudiz
  • 7,211
  • 22
  • 78
  • 156