title: Insert from select or update from select with commit every 1M records
body: <p>I've already seen a dozen such questions but most of them get answers that doesn't apply to my case.</p>
  
  <p>First off - the database is am trying to get the data from has a very slow network and is connected to using VPN.</p>
  
  <p>I am accessing it through a database link.</p>
  
  <p>I have full write/read access on my schema tables but I don't have DBA rights so I can't create dumps and I don't have grants for creation new tables etc.</p>
  
  <p>I've been trying to get the database locally and all is well except for one table.</p>
  
  <p>It has 6.5 million records and 16 columns.
  There was no problem getting 14 of them but the remaining two are Clobs with huge XML in them.</p>
  
  <p>The data transfer is so slow it is painful.</p>
  
  <p>I tried 
  insert based on select
  insert all 14 then update the other 2
  create table as
  insert based on select conditional so I get only so many records and manually commit</p>
  
  <p>The issue is mainly that the connection is lost before the transaction finishes (or power loss or VPN drops or random error etc) and all the GBs that have been downloaded are discarded.</p>
  
  <p>As I said I tried putting conditionals so I get a few records but even this is a bit random and requires focus from me.
  Something like : </p>
  
  <pre><code>Insert into TableA 
  Select * from TableA@DB_RemoteDB1 
  WHERE CREATION_DATE BETWEEN to_date('01-Jan-2016') AND to_date('31-DEC-2016')
  </code></pre>
  
  <p>Sometimes it works sometimes it doesn't. Just after a few GBs Toad is stuck running but when I look at its throughput it is 0KB/s or a few Bytes/s.</p>
  
  <p>What I am looking for is a loop or a cursor that can be used to get maybe 100000 or a 1000000 at a time - commit it then go for the rest until it is done.</p>
  
  <p>This is a one time operation that I am doing as we need the data locally for testing - so I don't care if it is inefficient as long as the data is brought in in chunks and a commit saves me from retrieving it again.</p>
  
  <p>I can count already about 15GBs of failed downloads I've done over the last 3 days and my local table still has 0 records as all my attempts have failed.</p>
  
  <p>Server: Oracle 11g</p>
  
  <p>Local: Oracle 11g</p>
  
  <p>Attempted Clients: Toad/Sql Dev/dbForge Studio</p>
  
  <p>Thanks.</p>