Solved

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

Posted on 2011-03-23
10
454 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
pl/sql - query very slow 26 88
ER Diagram 3 42
How to drop system generated virtual column in a table in12c 15 118
Migration from sql server to oracle (IF then else condition ) 13 43
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

749 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