Solved

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

Posted on 2011-03-23
10
436 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
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 76

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
 
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 76

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
CREATE TABLE syntax 4 44
Oracle SQL Select Statement 19 58
Oracle PL/SQL syntax 4 52
query in Oracle forms Builder 2 26
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now