How to kill session in Oracle

In the day-to-day maintenance of Oracle, the following two situations are frequently required for our DBA kill session:

1. App complained that their application hang live, in the database query that their session by other sessions block, at this time to kill the other session, so that app application can run in time.

2. App ran half way, suddenly decided not to run, and needed a DBA to kill their session.

Here's how I often use the kill session:

1. At the database level, alter system kill ' SID, Serial# '

2. In some exceptional cases, Method 1 is not available and executes kill-9 at the OS level

Using the above methods often encountered the problem is: For those who have been running for a long time, not a moment can kill off, and sometimes have to wait for a long time, this time can only be silly in that, when the app side of the people asked "session disappeared?" How long will it take if it doesn't disappear? "And so on and so on, always can't give them the exact answer, until today read ask Tom an article, only to find a solution."

Http://asktom.oracle.com/pls/asktom/f? p=100:11:0::::P 11_question_id:4974573906087

1. Alter system kill ' SID, Serial# ' What does this statement do?

Whenever the following statement is executed:

A105024@o02dms1>alter system kill session ' 524,24148 ';

System altered.

Can always quickly return the results of system altered, many people mistakenly think that the session has been killed, in fact, the statement is only issued a command to tell this session: You were killed dropped, if the session is idle state, it Can end immediately, but if the session is doing a transaction, it needs to roll back the transaction and release the lock before it ends.

Therefore, for sessions that require a long rollback to be killed, after executing alter system kill session, its state becomes killed, such as:

Select username, status from V$session where sid= 524;

SID STATUS

--------------- ----------

524 killed

2. How long does it take to killed state to disappear?

How long does it take to evaluate the session of the killed state before it disappears? We can query the v$transcation in the used_ublk, such as used_ublks the current value is 500, 1 minutes after 400, then you can estimate that the session will need 400/(500-400) = 4 minutes to end the time, The following is an actual example:

Select Used_ublk from V$transaction;

Used_ublk

----------

2262

1 row selected.

Sql>/

Used_ublk

----------

1430

1 row selected.

Sql>/

Used_ublk

----------

1103

1 row selected.

Always monitor the value of USED_UBLK, as long as it is not equal to 0, the session is still in the killed state:

Select username, status from V$session where sid= 524;

SID STATUS

--------------- ----------

524 killed