Oracle quick and complete kill session (reprint)

Reprint: http://www.cnblogs.com/kerrycode/p/4034231.html

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 (reprint)