Bulk Delete of Records in
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
I need to write a procedure to delete records and at every 1000
records need a commit.
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.
Here is a sample of procedure to do so :
CREATE OR REPLACE PROCEDURE CLEAN_TEMP IS tmp_current integer; tmp_count
tmp_current := 0;
select count(*) into tmp_count from temp ;
exit when tmp_current > tmp_count ;
delete from temp where rownum between tmp_current and tmp_current
tmp_current := tmp_current + 1000;
WHEN NO_DATA_FOUND THEN
WHEN OTHERS THEN
-- Consider logging the error
and then re-raise
Have a Oracle Question
Do you have
an Oracle Question?
Certification, Database Administration, SQL, Application, Programming Reference
Application Hints and Tips
Database, SQL, Application, Programming Tips
All the site contents are Copyright © www.erpgreat.com
and the content authors. All rights reserved.
All product names are trademarks of their respective
The site www.erpgreat.com is not affiliated with or endorsed
by any company listed at this site.
Every effort is made to ensure the content integrity.
Information used on this site is at your own risk.
The content on this site may not be reproduced
or redistributed without the express written permission of
www.erpgreat.com or the content authors.