I have a query that is deleting over 3.5 million records from a table that had grown to almost 9 million records. The tablespace that the table is in is sized to 6 gigs, but is only barely over 50% full. There are other tables in this tablespace, but this table is the largest.
I am the only one on this instance, I am monitoring my rollback segments and they are steadily increasing (83% of 2 gigs). I am looking at the V$SESSION view and the LAST_CALL_ET is steadily incrementing. I started this query at 1:30 p.m. Monday afternoon, and based on those two points, I gather it is still running.
Is there any other way to validate that the query is running and get an estimated percentage complete or an estimated time until completion?
Any help would be greatly appreciated...
It is very difficult to delete 3.5 M rows at a time.
Try to delete 100,000 rows at a time so that Rollback segment will not grow. Also you can estimate the time.
If you are on 8i and are sure that you do not need to rollback the transaction, why cann't you use descrete transaction ? That will not create any rollback information and the DELETE will speed up.