Oracle: killed session continue running

Question

I can't simply stop it and it continues to read blocks and use rollback segments. It's a simple select but I fear it won't stop...

The session is marked as killed. What can I do?

I've found some extra info on the following link: http://oracleunix.wordpress.com/2006/08/06/alter-system-kill-session-marked-for-killed-forever/ but if I launche the following query it returns 241 records. What does it mean?

SELECT   spid
  FROM   v$process
 WHERE   NOT EXISTS (SELECT   1
                       FROM   v$session
                      WHERE   paddr = addr);

Answer 1

If the session you kill had a large open transaction, it will have to roll back all those changes. So, you should see amount of undo being used go down, not up.

Try this query:

select vt.used_ublk from v$transaction vt, v$session vs where vs.taddr=vt.addr and vs.sid=&&sid;

Now, if you run the above query multiple times in succession, is used_ublk falling or increasing? If it's falling, then the session is rolling back.

Hope that helps.

Answer 2

I'm going to assume that you session you killed was just a select as you state and that you're operating on a *nix variant.

If you're running an update or delete then waiting for the rollback to complete would be best. You can check the amount of rollback by using the following query, which I've shamelessly stolen from orafaq because I don't remember these things off the top of my head:

select rn.Name "Rollback Segment", rs.RSSize/1024 "Size (KB)", rs.Gets "Gets"
     , rs.waits "Waits", (rs.Waits/rs.Gets)*100 "% Waits"
     , rs.Shrinks "# Shrinks", rs.Extends "# Extends"
  from sys.v_$rollName rn, sys.v_$rollStat rs
 where rn.usn = rs.usn;

First off a select shouldn't be using using rollback... if it does then you've probably got a function that does some DML somewhere, which isn't a very good idea. You also don't mention whether this select is using a database link, if it is that clears things up a little bit.

If the select is not using a database link and is not doing any DML, then the link you've found will do everything you need. Your 241 rows, should mostly be identical - there may be more than one value if you have more than one process that has this problem. I would change the query to:

select p.*
  from v$process p
  left outer join v$session s
    on p.addr = s.paddr
 where s.saddr is null

This means that you can check the username that owns the process the terminal it was run from and program that is running before doing anything drastic. You don't want to go around killing the wrong thing.

You can then go direct to your box and issue the sigterm kill 1234. This issues a terminate signal to the process at the level of your OS and should get rid of it.

As an addendum, if your session is using a database link then killing it on the box it was running from is normally not enough. You may also have to kill it on the box that you're selecting from. Try the standard Oracle kill first and then scale it to OS level.

This should work. However, it's possible to get a lot more drastic; I've had to recently after a slave VM started accepting connections incoming and then not sending an error or returning a value.

Warning: The more violent you get to the box the more violent it will be to you and the more likely things are to go wrong.

The next step up from a sigterm is a sigkill. This is a signal to the OS to kill a process without asking any questions. On *nix this is kill -9 1234. This should rarely be necessary. If you were doing DML it will stop any rollback and may make it difficult to recover the database to a consistent state in the event of failure.

If this still doesn't work then you have major problems. In the example given with the VM we ended up doing the following in order to stop the problem. Most of these are not recommended :-).

1. Oracle - alter system kill 123
2. OS - kill 1234
3. OS - kill -9 1234
4. Oracle - shutdown immediate - this is actually politer than kill -9 ..... It doesn't send a sigkill to the OS and waits for processes to rollback etc. But it's always good to be polite to your database.
5. Oracle - shutdown abort - this is about the same as a sigkill. It's a signal to the database to stop everything immediately and die ( confusing terminology I know ).
6. OS - reboot
7. Yes that's right, reboot didn't work. Once you've reached this stage you better hope you're using a VM. We ended up deleting it...