Q
Problem solve Get help with specific problems with your technologies, process and projects.

Is there a way to stop a rollback?

I have a table with 10 partitions. Each partition has 1 milion rows. I wanted to delete rows with rid=1, which are in partition 1. It was taking a long, long time. So I killed the session. Now my database is rolled back, all the rows deleted.

Is there a way to stop the rollback? If I shutdown immediately, I guess it will take a long time to shutdown. If...

I shutdown abort, when I restart the database, it will do a long, long recovery, right?

I don't care if the data is deleted or not. I need a way to issue "alter table xyz nologging" without triggering the "ORA-00054 resource busy ..."

You can't stop the system from rolling back an aborted transaction. Since you killed the session, the transaction was aborted and the system has no choice but to rollback any changes you made. This system will not decide for you which abort transactions to keep and which to get rid of. So it rolls back all of them. Even if you shutdown the database and bring it back up, it will still perform rollback after the database is started. You'll just have to wait for it to finish. Once it is finished rolling back, you won't get the ORA-00054 error.

Next Steps

What do you think about this answer? E-mail the editors at editor@searchDatabase.com with your feedback.

The Best Oracle Web Links: tips, tutorials, scripts, and more.

Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!

Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.

Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

This was last published in April 2002

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close