How to stop oracle undo process?

Question

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 1

You can't stop the process of rolling back the transaction because doing so would leave the database in an inconsistent state.

When you are executing a long-running delete process, Oracle will likely be writing the changed blocks to your data files before you decide whether to commit or rollback the transaction. If you interrupted the process in the middle of executing the transaction, there will be some changed blocks on disk, some changed blocks in memory, and some unchanged blocks. Rolling back the transaction is the only way to return the database to the state it was in before you started executing the DELETE statement.

Answer 2

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.