How can I follow progress of mass delete

[nlefel]

I'm deleting 8 000 000 entries from a table of 24 000 000 records.
The application just uses

delete from big_table where col1 = 'abc';

I can't change the application.
Can I check on the progress of the delete or must I just wait
(6 hours or so).
Oracle 8.1.7 in use with Unix. No hardware upgrade possible...

PS. Indices are first dropped and subsequently rebuilt. Its
not a question of speed - just progress inside the black box.

Thanks in anticipation !
Nick

[TomazZ]

If delete is using full table scan, then you can see progress in v$session_longops.

[nlefel]

I have now seen the way v$session_longops
deals with a select count(*)
But my long delete uses an index...

an anyone suggest another way to track its
progress ??

Cheers
Nick

[Edited by nlefel on 08-23-2002 at 04:12 AM]

[nlefel]

Using v$session and v$sqltext I can "see" whats going on.
But I don't get any idea of progress .
Does anyone see a way ahead. My users are waiting
(but getting jumpy)...

Cheers
Nick

[SANJAY_G]

Are you commiting in between or deleting at one shot?
If you are commiting after certain no. of records you can monitor the count(*). If you are not commiting monitor the growth of Rollback segment used by this transaction. At least you can make sure that the transaction is doing somehing or simply hanging.

HTH
Sanjay

[TomazZ]

You can also create trigger on delete for each row and call nextval on sequence.

Then you can call nextval on sequence from another session to see how many rows you have already deleted.

But this makes some overhead - delete will be even slower.