Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 479
  • Last Modified:

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

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
Alaska Cowboy
Asked:
Alaska Cowboy
  • 5
  • 2
  • 2
  • +1
3 Solutions
 
slightwv (䄆 Netminder) Commented:
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
 
AkenathonCommented:
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
 
Alaska CowboyAuthor Commented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Franck PachotCommented:

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
 
Alaska CowboyAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
The rollback happened if you issued it or not.

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

0
 
Alaska CowboyAuthor Commented:
Franck, thanks again.
0
 
Alaska CowboyAuthor Commented:
increase points.
0
 
AkenathonCommented:
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
 
Alaska CowboyAuthor Commented:
Akenathon, thanks for the followup, good info.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now