Friday, November 23, 2012

Oracle DB: deleting a HUGE table a chunk at a time in a loop

This is just an example, of course TRUNC will be much faster... but if you need to select specific records, you have no choice.....

DECLARE
  nCount  NUMBER; 
  sql1 VARCHAR2(2000);
BEGIN
  
  nCount := 0;
  sql1 := 'delete from WLI_QS_REPORT_DATA where rownum < 10000';
  LOOP
    
    EXECUTE IMMEDIATE sql1;
    nCount := sql%rowcount;
    DBMS_OUTPUT.PUT_LINE('deleted records: ' || to_char(ncount) );
    commit;    

    EXIT WHEN nCount = 0;

  END LOOP;
end;
/




No comments: