use of cursors for deletion is bad idea.it degrades the performance even more. & more ever u want to commit tran afer 50000 records...that is prone to SNAPSHOT too old as suggested by JMODIC.
i think deletion using rownum & looping is , i can say, better than using cursors.
If number of records in table that needs deletion is more than what it has to retain after deletion than better go for som TEMP table insertion of data that u want to retain.....truncate current table & insert back the records from TEMP table....
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
Originally posted by stmontgo create a new table,
insert as select into the new table from the old,
backup the original table,
drop the original table
rename the new table to the old table,
rebuild indexes grants etc on the new table
rbs is the least of your concerns , mass deletes
can be a serious performance hit as such an action
does no reset the highwater mater and subsequent
scans will scnn up the high water mark even though
those blocks may be empty.
How do I backup the original table? Can you give me a sample code for the above tasks?