In the Oracle database, sometimes the ALTER SYSTEM kill session ' sid,serial# ' is used to kill a session process, but after the session is killed with this SQL statement, the database does not immediately release the associated resources. Sometimes you will find that the locked resource is not released for a long time, even if the session state is "killed", it will still block other sessions.
Below, based on Eygel's "Kill session in Oracle", construct a case to see what the kill session did. As shown below
Session 1:
Sql> Conn Etl/etl
Connected.
sql> Update test set status= ' invalid ';
55944 rows updated.
sql> update test2 set dropped= ' Y ';
3090 rows updated.
Session 2:
Sql> Show User
USER is "SYS"
sql> Update etl.test2 set dropped= ' Y ';
3090 rows updated.
Session 3
Sql> Select Saddr,sid,serial#,paddr,username,status from v$session where username =upper (' ETL ') or username =upper (' SYS ');
Saddr SID serial# paddr USERNAME STATUS
---------------- ---------- ---------- ---------------- ------------------------------ --------
000000025c233b00 33353 000000025f1d1fc8 ETL INACTIVE
000000025c23a608 Notoginseng 11448 000000025f1d27b0 SYS ACTIVE
000000025C24BC50 54311 000000025f1d5f08 SYS ACTIVE
Sql> alter system kill session ' 27,33353 ';
System altered.
Sql> Select Saddr,sid,serial#,paddr,username,status from v$session where username =upper (' ETL ') or username =upper (' SYS ');
Saddr SID serial# paddr USERNAME STATUS
---------------- ---------- ---------- ---------------- ------------------------------ --------
000000025c233b00 33353 000000025c21a0b0 ETL killed
000000025c23a608 Notoginseng 11448 000000025f1d27b0 SYS ACTIVE
000000025C24BC50 54311 000000025f1d5f08 SYS INACTIVE
as shown below, after I kill two of these sessions, the addresses of both sessions become 000000025c21a0b0 (see paddr column). When you kill a session in Oracle, Oracle simply points the paddr of the session to the same virtual address. V$process and V$session lost their connection and the process was interrupted. Oracle then waits for Pmon to clear the sessions. So it usually takes a long time to wait for a session that is marked as killed to exit . If the process is killed at this time, re-attempt to perform the task, you will immediately receive a prompt for progress interrupt, and process exits, at which time Oracle will start Pmon to clear the session. This is handled as an exception interrupt.
Sql> alter system kill session ' 63,54311 ';
System altered.
From V$session where username =upper (' ETL ') or username =upper (' sys ');
Saddr SID serial# paddr USERNAME STATUS
---------------- ---------- ---------- ---------------- ------------------------------ --------
000000025c233b00 33353 000000025c21a0b0 ETL killed
000000025c23a608 Notoginseng 11448 000000025f1d27b0 SYS ACTIVE
000000025C24BC50 54311 000000025c21a0b0 SYS killed
We find the address of the process based on the SQL below, then find the corresponding SPID in v$process and then kill the process from the operating system.
Sql> Select P.addr from V$process p where PID <> 1
2 minus
3 Select s.paddr from V$session s;
ADDR
----------------
000000025f1d1fc8
000000025f1d5f08
where username =upper (' ETL ') or username =upper (' sys ');
Saddr SID serial# paddr USERNAME STATUS
---------------- ---------- ---------- ---------------- ------------------------------ --------
000000025c233b00 33353 000000025c21a0b0 ETL killed
000000025c23a608 Notoginseng 11448 000000025f1d27b0 SYS ACTIVE
000000025C24BC50 54311 000000025c21a0b0 SYS killed
sql> Select addr, pid, spid, username from v$process where addr in (' 000000025f1d1fc8 ', ' 000000025f1d5f08 ');
ADDR PID SPID USERNAME
---------------- ---------- ------------ ---------------
000000025F1D1FC8 12959 Oracle
000000025f1d5f08 12971 Oracle
Sql>! Kill-9 12959
Sql>! Kill-9 12971
From V$session where username =upper (' ETL ') or username =upper (' sys ');
Saddr SID serial# paddr USERNAME STATUS
---------------- ---------- ---------- ---------------- ------------------------------ --------
000000025c23a608 Notoginseng 11448 000000025f1d27b0 SYS ACTIVE
There are three ways to kill a session process in an Oracle database:
1:alter SYSTEM KILL SESSION
About kill session Clause, as shown in the official documentation below, the ALTER system kill session is not actually a real kill conversation, it simply marks the session as terminated. Wait for the Pmon process to clear the session.
The KILL session clause lets you mark a session as terminated, roll back ongoing transactions, release all SESSION locks, and partially recover session resources. To use this clause, your instance must has the database open. Your session and the session to be terminated must is on the same instance unless you specify Integer3. You must identify the session with the following values from the V$session view:
For Integer1, specify the value of the SID column.
For Integer2, specify the value of the serial# column.
For the optional Integer3, specify the ID of the the instance where the target session is killed exists. You can find the instance ID by querying the gv$ tables.
If the session is performing some activity this must be completed, such as waiting for a reply from a remote database or R Olling back a transaction, then Oracle Database waits for this activity to complete, marks the session as terminated, and Then returns control to you. If The waiting lasts a minute, then Oracle Database marks the session to being terminated and returns control to you with a M Essage The session is marked to be terminated. The Pmon background process then marks the session as terminated, when the activity was complete.
Whether or not the session has a ongoing transaction, Oracle Database does not recover the entire session state until the Session user issues a request to the session and receives a message, the session has been terminated.
You can use the ALTER SYSTEM KILL session ' sid,serial# ' IMMEDIATE to quickly rollback a thing, release a related lock on a session, and immediately return control of the current session.
Specify IMMEDIATE to instruct Oracle Database to roll back ongoing transactions, release all session locks, recover the en Tire session state, and return control to you immediately.
2:alter SYSTEM DISCONNECT SESSION
ALTER system DISCONNECT session kills a dedicated server (dedicated server) or shared Server connection session, which is equivalent to killing a process from the operating system. It has two options post_transaction and immediate, where post_transaction means to disconnect the session after the transaction is complete, immediate represents an interrupt session, and immediately rolls back the transaction.
sql> ALTER SYSTEM DISCONNECT SESSION ' sid,serial# ' post_transaction;
sql> ALTER SYSTEM DISCONNECT SESSION ' sid,serial# ' IMMEDIATE;
3:kill-9 spid (Linux) or Orakill oracle_sid spid (Windows)
You can use the following SQL statement to locate the corresponding operating system process spid and then kill it. Of course, killing the operating system is a dangerous thing to do, especially if it's not manslaughter. So be sure to check it carefully before you execute it.
SET Linesize 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45
SELECT s.inst_id,
S.sid,
s.serial#,
P.spid,
S.username,
S.program
From gv$session s
JOIN gv$process p on p.addr = s.paddr and p.inst_id = s.inst_id
WHERE S.type! = ' BACKGROUND ';
In the database if you want to completely kill a session, especially a large transactional session, it is best to use the ALTER SYSTEM DISCONNECT Session immediate or use the following steps:
1: First kill the process at the operating system level.
2: Kill SESSION inside the database
or vice versa. This can quickly terminate the process and free up resources.
Resources:
Http://www.eygle.com/faq/Kill_Session.htm
Http://www.oracle-base.com/articles/misc/killing-oracle-sessions.php#unix
Http://www.eygle.com/archives/2005/10/oracle_howto_kill_session.html
Http://blog.sina.com.cn/s/blog_6d6e54f70100zfqp.html
http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_2013.htm#SQLRF53047
Oracle quick and complete kill session