Please suggest a better way

[dbasan]

Thanks all.
Tamil / Ken : How do I delete just the locked rows ? And, if I may know, why do you think the rownum based solution will not work ? Any technical reason for that ?

When you do read 1000 rows based on the condition to insert into another table use the same to delete the records.

Records may change the order as stored when using rownum, if that table undergoes DML's in between your archiving. Use primary key column if available or timestamp column would be the right way to use in where conditions.