CGH CGH - 8 days ago 7
SQL Question

How to stop oracle undo process?

I want to know how to stop undo process of oracle ? I tried to delete millions of rows of a big table and in the middle of process I killed session but It started to undo delete and for a bout two hours database got dramatically slow. I didn't want the undo process to be continued. Is there any way to stop it ?

Answer

Row-by-row delete processes can, as you've found, be exceedingly slow. If the deletions are all done in a single transaction, as appears to be the case here, they can become even slower. You might want to consider the following options:

  1. If you're deleting all the rows in the table you might want to consider using the TRUNCATE TABLE statement.

  2. If you're not deleting all the rows in the table you should probably change your procedure to COMMIT after a certain number of rows are deleted.

In the meantime you're going to have to wait until that rollback process completes.

Share and enjoy.