title: Delete records from Oracle in sets
body: <p>I am facing issue deleting set of records from a table named links_data because of huge set of data to be deleted and the Oracle DB ran out of temp space.</p>
  
  <p>For the same with little knowledge of mine on Stored Procedure I wrote the below code. 
  But the Stored Procedure looks to be running slow as well. </p>
  
  <p>The scenario is that I need to delete the rows from links_data table where the nm_folder column in links_data table has foreign key relation with link_folders table.  nm_folder in link_folders table is the primary key and I intend to delete 10000 records at a time and commit and then again delete 10000 records and commit.</p>
  
  <p>Kindly anyone please help me optimize this query. Thanks &amp; Regards,</p>
  
  <pre><code>Declare
  
  Type ty_link_floder is table of number;
  
  tble_id_folder ty_link_floder;
  
      Cursor c_data is
      select id_folder from link_folders where nm_folder='User Hotlist';
  
      Begin
      OPEN c_data;
  
        LOOP
          FETCH c_data
          BULK COLLECT INTO tble_id_folder LIMIT 10000;
          EXIT WHEN tble_id_folder.count = 0;
                      FORALL i IN tble_id_folder.first .. tble_id_folder.last
          DELETE FROM links_data
          WHERE id_folder = tble_id_folder(i);
          COMMIT;
  
          -- Process contents of collection here.
  
          DBMS_OUTPUT.put_line(tble_id_folder.count || ' rows deleted from links_data table so far');
          tble_id_folder.delete;
        END LOOP;
        CLOSE c_data;
      Exception
       When others Then
        Commit;
        Raise;
      End;
  </code></pre>