How can I follow progress of mass delete
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: How can I follow progress of mass delete

  1. #1
    Join Date
    Dec 2001
    Posts
    7
    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

  2. #2
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    If delete is using full table scan, then you can see progress in v$session_longops.

  3. #3
    Join Date
    Dec 2001
    Posts
    7
    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]

  4. #4
    Join Date
    Dec 2001
    Posts
    7
    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

  5. #5
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    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

  6. #6
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width