Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-03-23
10
Medium Priority
?
477 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 200 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 200 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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 15

Accepted Solution

by:
Franck Pachot earned 400 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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 configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

597 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