Solved

How would oracle d.b. handle a botched rollback ?

Posted on 2011-03-23
10
462 Views
Last Modified: 2012-05-11
I deleted a large volume of data then tried to rollback and had to kill the session. How would oracle 10.2.0.3.0 handle this ?

I deleted data from 13 tables, one of which deleted 4.2 M rows. So I killed the delete (in Pl*Sql Developer). The kill of my query worked and then I clicked the button to rollback, at which time the Pl*Sql Developer window froze up.

So I killed the Pl*Sql Developer window via Windows Task Manager, but the session was still running in Oracle. I then tried to kill the session (twice) via Sql*Plus using "exec manage_sessions.kill ('SID','SERIAL NUMBER')". That did NOT kill the session. In the morning it was finally gone.

After some initial triage, it looks like the deletes were NOT committed, as I had hoped was the case.

But I just wondered how Oracle would handle this.
0
Comment
Question by:Alaska Cowboy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 50 total points
ID: 35200667
Oracle should automatically rollback all uncommited transactions from the killed session.

There is nothing you need to do except wait for it to finish.
0
 
LVL 11

Assisted Solution

by:Akenathon
Akenathon earned 50 total points
ID: 35200774
To add to "how Oracle handles this": There's a mandatory background process on every instance, called Process Monitor (PMON) which among other things cleans up after killed sessions and/or server processes. The uncommited orphan transaction you created will be rolled back, the locks it had will be released, and the undo records discarded after the rollback.

Side note: if you are deleting a significant portion of a very big table, maybe you could copy the data you want to keep to a new table, create the necessary indexes, gather statistics, drop the old one and rename the new one to the original name. It could be much faster, give it a try...
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 35200808
slightwv, thank you, that's what I saw (no commits), but just wanted to understand.

Akenathon, thank you as well. in this case, the 4.2 M records is actually only 10 - 20% of the table volume, so it's not practical. but a good tip to consider for other situations.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 15

Accepted Solution

by:
Franck Pachot earned 100 total points
ID: 35202455

Hi,

That is not exact: PMON is not involved in killed sessions.
A killed session is doing its rollback itself. This is why the session was there until the morning.

SQL*Developer was stuck because a kill session waits for the feedback. You can use 'kill immediate' to avoid that.

PMON is involved only when the session is disconnected: it process is killed, ord tcp/ip disconnected, or alter session disconnect...
Then PMON, detecting disconnected session, will do the rollback itself. But that was not the case here.

Another process can do the rollback: If instance crashes or shutdoen abort, then SMON, doing the recovery will rollbacl all non commited transactions.

Regards,
Franck
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 35202541
Franck, thanks for the info.

When this happened in Pl*Sql Developer, first I clicked the "Break" button, that was clean, then I clicked the "Rollback" button. would it have been cleaner to type "Rollback" at the command line ? (I assume no). I guess I should have just waited (probably a couple of hours) to let the rollback complete rather than killing my Pl*Sql Developer session.

And when a session is killed, it will say, "Hold on, I've got some clean-up to do, then I'll go away", right ?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35202564
The rollback happened if you issued it or not.

Some sessions will me 'marked' for kill and may not go away immediately.

0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 35202591
Franck, thanks again.
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 35202598
increase points.
0
 
LVL 11

Expert Comment

by:Akenathon
ID: 35202661
I've re-read the original post and I agree that Franck is correct: if the server process' thread was not terminated at any point (i.e. alter session disconnect immediate or OS killing), there is no PMON cleanup involved -the server process does a "normal" rollback itself.

Issuing the rollback command by clicking the button or typing the command is the same thing -the button issues the same comand anyway. Killing the PL/SQL Developer GUI is not relevant, the server process' thread will continue doing the rollback one way or the other. Same thing if you kill the session, it rolls back its ongoing transaction before exiting (unlike disconnect immediate).
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 35202713
Akenathon, thanks for the followup, good info.
0

Featured Post

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question