How to kill a session quick

I used to ran into this problem:

A user logs in and locks several tables. Even if I use "Aler system kill session ...", the session is only marked as "killed.". How do I make sure the session is killed quickly? Someone will suggest me to re-start the instance. But sometimes that's not an option.

Since you didn't specify, I'll assume you're on Solaris. Just kill the backend process.

If you are in Unix, try Unix kill -9.

Good Luck

1. If on Unix, how do I know which process belong to which user. I need to be able to identify, say, the process of "SCOTT" and then kill -9 xxx.

2. If this is on NT, does this work?

Quote:

---

Originally posted by dba_admin
1. If on Unix, how do I know which process belong to which user. I need to be able to identify, say, the process of "SCOTT" and then kill -9 xxx.

2. If this is on NT, does this work?

---

1. Check SPID column in V$PROCESS to get Unix process ID.

2. NT Task manager doesn't breakdown the information to thread level.

Sanjay

I am not sure if kill -9 is the best way, I have had pretty bad experience on tru64 killing the shadow process. If I kill from Oracle pmon goes slowly and freesup resources if I kill the shadow pmon process eats up 100% CPU :o

If you are running on Unix, try to get PID of the required process, and then Use TOAD to kill that session, i.e. PID on Unix is same as SPID in TOAD. So, you can also find which process is running by which user through toad and then kill it from either TOAD or kill -9 .

Thanks

Still not found a sure-fire way to do this in NT.

You can kill the locking sessions but they are only marked as being killed - and not actually killed. I had to reboot the instance.

Anyone know a good method?

Quote:

---

Originally posted by dba_admin
I used to ran into this problem:

A user logs in and locks several tables. Even if I use "Aler system kill session ...", the session is only marked as "killed.". How do I make sure the session is killed quickly? Someone will suggest me to re-start the instance. But sometimes that's not an option.

---

The session is killed immediately when you kill an active session. Marked as killed comes when you kill an inactice session. Marked for kill does not mean, the user is not killed. The user is killed, but Oracle marks the status as "killed" and the user details can be seen in v$session view. It does not mean, the user is still connected. The user is informed "session killed" when the user sub sequently queries the database and the user details are removed from v$session.

It's like

ACTIVE session killed:
1. One active user is killed.
2. User is disconnected from database and a message is displayed to user end terminal.
3. User details are not available in v$session view.

INACTIVE session killed:
1. One inactive user is killed.
2. User is disconnected from database.
3. User is not informed about disconnection.
4. User details are available in v$session view with status as "KILLED"
5. User tries to query the database and is informed of killing.
6. User details are rfemoved from v$session view.


All these things are for a typical dedicated server database. Things may vary for MTS. And there is something to do with inband and outband connections. These connection types make a lot of difference when we kill the session.

Can somebody refresh me with the details of these breaks??? :cool:

That was a normal Rollback being done - U need to wait for it being done

else in other cases U can try

ALTER SYSTEM KILL &sid,&serial# IMMEDIATE;

Thanks,
Iyyappan.M

This shows you how to kill sessions completely in UNIX and NT.

http://www.oracle-base.com/Articles/...leSessions.asp

In NT you need to use the orakill utility to kill the offending thread. Remeber, Oracle on NT is a single process with multiple threads, so you actually kill a thread not a process.

Cheers

For NT use Microsoft's qslice utilty in order to get the spid of a processs.

Ravinder