Question:
I need to delete 30000000 (3 crore)  records out of 10 Crore records from a table with where condition. There is no index on the search column.
I need to write a procedure to delete records and at every 1000 records need a commit. 

Solution:

We can use the following command to do so:

Delete from table_name where Column_Name ='condition' and rownum<1001; 
commit; Use this command in loop so that every 1000 records can be deleted and commit will be also done.

or

Here is a sample of procedure to do so : 

CREATE OR REPLACE PROCEDURE CLEAN_TEMP IS tmp_current integer; tmp_count integer; BEGIN
   tmp_current := 0;
   select count(*) into tmp_count  from temp ;

loop
  exit when tmp_current > tmp_count ;
  delete from temp where rownum between tmp_current and tmp_current + 1000;
  tmp_current := tmp_current + 1000;
  dbms_output.put_line(tmp_current);
  commit;
END loop; 

   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END CLEAN_TEMP;
/

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips