One .1  BLOG Document structure chart

wpsCEE4.tmp 

 

One .2   Preface

 

One .2.1   Guidance and notes

Technology enthusiasts , After reading this article , You can master the following skills , You can also learn something else you don't know ,~O(∩_∩)O~:

① killed How the state of the session is released ( How to find the background process )-- a key ?

②  Granted to ordinary users can kill Own users session Authority

③ kill session and disconnect session The difference between

④ v$session.CREATOR_ADDR The use of columns

⑤ Yes inactive The processing of conversation (1、sqlnet.ora Set in file expire_time  Parameters  2、 user profile Of idle_time  Parameters  3、 Find a session that hasn't responded for a long time and kill fall )

⑥ v$session.LAST_CALL_ET Use

⑦ PMON The clean-up cycle of is an implicit parameter "_PKT_PMON_INTERVAL" Introduce

 

  Tips:

① In this paper ITpub(http://blog.itpub.net/26736162) And blog Park (http://www.cnblogs.com/lhrbest) There are synchronous updates

② All the code used in this article , Related software , Related information ,MOS Please go to Xiaomai Miao's cloud disk to download the information (http://blog.itpub.net/26736162/viewspace-1624453/)

③  If the article code format is wrong , Recommend Sogou 、360 or QQ browser , You can also download pdf Format to view ,pdf Document download address :http://blog.itpub.net/26736162/viewspace-1624453/

④  This article BLOG I use gray background and pink font to show the output part of the command that needs special attention , For example, in the following example ,thread 1 The maximum archive log number for is 33,thread 2 The maximum archive log number for is 43 It's something that needs special attention ; Commands are usually marked with yellow background and red font ; Comments on the code or the output part of the code are generally in blue font .

 

  List of Archived Logs in backup set 11

  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time

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

  1    32      1621589    2015-05-29 11:09:52 1625242    2015-05-29 11:15:48

  1    33      1625242    2015-05-29 11:15:48 1625293    2015-05-29 11:15:58

  2    42      1613951    2015-05-29 10:41:18 1625245    2015-05-29 11:15:49

  2    43      1625245    2015-05-29 11:15:49 1625253    2015-05-29 11:15:53

 

 

 

 

[ZHLHRDB1:root]:/>lsvg -o

T_XDESK_APP1_vg

rootvg

[ZHLHRDB1:root]:/>

00:27:22 SQL> alter tablespace idxtbs read write;

 

 

====》2097152*512/1024/1024/1024=1G 

 

 

 

If there are any mistakes or imperfections in this article, please correct them ,ITPUB Message or QQ All can , Your criticism is the biggest motivation of my writing .

 

 

One .2.2   Links to related reference articles

 

disconnect session and kill session The difference between :http://blog.itpub.net/26736162/viewspace-1979223/

One .2.3   Brief introduction

Today, I used alter system kill session ‘xxx,xxx’ The way , And after that ,v$session You can also find , Just ask me for help , Because I used to bring it with me immediate Of , They'll be released as soon as they're done ,v$session We can't find , Colleagues can only kill the background process now , but paddr Column is not associated with a background process , Look up the MOS There are still some ways , Sort it out , Share with you .

 

One .3   Related knowledge points literacy ( From the Internet + Personal summary )

One .3.1   Get several of the current conversation SQL

SELECT USERENV('SID') FROM DUAL;

SELECT SID FROM V$MYSTAT WHERE ROWNUM =1;

 

SELECT b.SID,

       b.SERIAL#

FROM   v$session b

WHERE  b.SID = USERENV('SID');

 

SELECT a.SID,

       b.SERIAL#

FROM   v$mystat  a,

       v$session b

WHERE  a.SID = b.SID

AND    rownum = 1;

 

SELECT a.SID,

       b.SERIAL# ,

       c.SPID,

       b.status

FROM   v$mystat  a,

       v$session b ,

       v$process c

WHERE  a.SID = b.SID

and b.PADDR=c.ADDR

AND    rownum = 1;

 

SELECT b.SID,

       b.SERIAL# ,

       c.SPID,

       b.status

FROM   v$session b ,

       v$process c

WHERE  b.PADDR=c.ADDR

AND   b.sid=???;

 

 

One .3.2  Session  Status description

Oracle session There are several states :

ACTIVE - Session currently executing SQL

INACTIVE

KILLED - Session marked to be killed

CACHED - Session temporarily cached for use by Oracle*XA

SNIPED - Session inactive, waiting on the client

 

 

A description of the State :

(1)active  A session in this state , It means that it is executing , Active .

Official documentation :

Any session that is connected to the database and is waiting for an event that does not belong to the Idle wait class is considered as an active session.

 

(2)killed A session in this state , Marked as deleted , Indicates that there is a mistake , Rolling back .

Of course , It also takes up system resources . The other thing is that ,killed It usually lasts for a long time , Also used windows Tools under pl/sql developer Come on kill fall , It doesn't matter , Use the command :alter system kill session 'sid,serial#' ;

 

(3)inactive  A session in this state indicates that it is not executing

This state is waiting for operation ( Waiting for what needs to be done SQL sentence ), Usually when DML The statement has been completed . But the connection is not released , This may be because there is no release in the program , If you are using middleware to connect , It could also be the configuration of middleware or bug  Lead to .inactive No impact on the database itself , But if the program is not in time commit, So it's going to take up too much conversation . Easy is DB  Of session  Reaching the limit .

Generally not handled inactive  State of session, If you reach session  The maximum of , Increase processes  and  sessions  Parameters . about Inactive State of session, You can set the expiration time :

(1)sqlnet.ora Set in file expire_time  Parameters

(2) Set user profile Of idle_time  Parameters

(3) Find a session that hasn't responded for a long time and kill fall

 

When you set resource_limit=true . adopt idle_time Limit session idle  Time .session idle Over set time , Status as sniped (v$session)., However OS Under the process It doesn't release , When session(user process)  Again with server process  Communications , The corresponding server process.

sqlnet.expire_time It's not the same ,Oracle Server  Send packet detection dead connection , If the connection is closed , Or not , Turn off the corresponding server process.

A combination of the above two , Reduce server process, prevent process exceed init$ORACLE_SID Limit value .

 

This article will focus on 3 There are two ways to explain it respectively .

 

One .3.2.1   clear inactive The session 3 Ways of planting

One 、  Set up sqlnet.expire_time

Can be in sqlnet.ora The document added sqlnet.expire_time This parameter is used to solve the problem , Set a number of minutes , This is a ORACLE Suggested DCD resolvent .

stay sqlnet.ora Set in file expire_time  Parameters

Description of this parameter on the official website :

http://download.oracle.com/docs/cd/B19306_01/network.102/b14213/sqlnet.htm

Oracle Database Net Services Reference 11g Release 2 (11.2)E10835-10

 

SQLNET.EXPIRE_TIME

Purpose

Use parameter SQLNET.EXPIRE_TIME to specify a the time interval, in minutes, to send a probe to verify that client/server connections are active. Setting a value greater than 0 ensures that connections are not left open indefinitely, due to an abnormal client termination. If the probe finds a terminated connection, or a connection that is no longer in use, it returns an error, causing the server process to exit. This parameter is primarily intended for the database server, which typically handles multiple connections at any one time.

sqlnet.expire_time  Principle :Oracle Server  Send packet detection dead connection , If the connection is closed , Or not , Turn off the corresponding server process.

 

Limitations on using this terminated connection detection feature are:

(1)It is not allowed on bequeathed connections.

(2)Though very small, a probe packet generates additional traffic that may downgrade network performance.

(3)Depending on which operating system is in use, the server may need to perform additional processing to distinguish the connection probing event from other events that occur. This can also result in degraded network performance.

 

Default :0

Minimum Value :0

Recommended Value :10

 

Example

SQLNET.EXPIRE_TIME=10

 

 

Two 、  Set user profile Of idle_time  Parameters

          

Oracle  user  profile  attribute

   http://blog.csdn.net/tianlesoftware/archive/2011/03/10/6238279.aspx

 

Be careful , To enable the idle_time  To start with RESOURCE_LIMIT Parameters . This parameter defaults to False. The official website is as follows :

 RESOURCE_LIMIT

Property

Description

Parameter type

Boolean

Default value

false

Modifiable

ALTER SYSTEM

Range of values

true | false

 

RESOURCE_LIMIT determines whether resource limits are enforced in database profiles.

Values:

TRUE:Enables the enforcement of resource limits

FALSE:Disables the enforcement of resource limits

 

3、 ... and 、  Find a session that hasn't responded for a long time and kill fall

About v$session in LAST_CALL_ET Column understanding :http://blog.itpub.net/26736162/viewspace-1762403/

 

according to v$session in LAST_CALL_ET The meaning of the column can be written as follows SQL Script , Not an hour to clean up 10 An hour of unresponsive conversation :

set sqlblanklines on

CREATE OR REPLACE PROCEDURE P_kill_session_LHR AS

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

        -- Created on 2013-06-25 12:05:07 by lhr

        --Changed on 2015-08-05 12:05:07 by lhr

        -- function:  kill 10 An hour ago conversation  , The alarm log will record the killed session information

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

 

    BEGIN

 

        -- IF to_char(SYSDATE, 'HH24') >= '20' OR

        --     TO_CHAR(SYSDATE, 'HH24') <= '08' THEN

 

        FOR cur IN (SELECT A.USERNAME,

                           A.LOGON_TIME,

                           A.STATUS,

                           A.SID,

                           A.SERIAL#,

                           A.MACHINE,

                           A.OSUSER,

                           'ALTER SYSTEM DISCONNECT SESSION ''' || a.SID || ',' ||

                           a.serial# || ''' IMMEDIATE' kill_session

                    FROM   v$session A

                    WHERE  A.STATUS IN ('INACTIVE')

                    AND    A.USERNAME IS NOT NULL

                    AND    A.LAST_CALL_ET >= 60 * 60 * 10) LOOP

 

            BEGIN

 

                EXECUTE IMMEDIATE cur.kill_session;

            EXCEPTION

                WHEN OTHERS THEN

                    NULL;

            END;

 

        END LOOP;

 

        -- END IF;

 

    EXCEPTION

        WHEN OTHERS THEN

            NULL;

    END P_kill_session_LHR;

/

 

 

 

BEGIN

    --DBMS_SCHEDULER.drop_job('JOB_P_kill_session_LHR');

    DBMS_SCHEDULER.CREATE_JOB(JOB_NAME        => 'JOB_P_kill_session_LHR',

                              JOB_TYPE        => 'STORED_PROCEDURE',

                              JOB_ACTION      => 'P_kill_session_LHR',

                              repeat_interval => 'FREQ=MINUTELY;INTERVAL=60',

                              ENABLED         => TRUE,

                              START_DATE      => SYSDATE,

                              COMMENTS        => ' Delete -- Every time 60 Check every minute ');

END;

/

 SELECT d.job_name,d.job_action,d.run_count FROM dba_scheduler_jobs d WHEREd.job_name='JOB_P_KILL_SESSION_LHR';

wpsCEF5.tmp 

 

Run log :

SELECT * FROM dba_scheduler_job_run_details  d WHERE d.job_name='JOB_P_KILL_SESSION_LHR';

wpsCEF6.tmp 

 

One .3.3  oracle Dead connection (DC) Talk to inactivity (INACTIVE)

 

This note explains the difference between a dead connection and an INACTIVE session in v$session.  It also discusses the mechanisms provided to automate the cleanup of each.

Here's the difference between a dead connection and an inactive conversation , We will also discuss the mechanism of automatic clearing .

 

 

Difference between INACTIVE sessions and Dead Connections

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

 

Dead connections and INACTIVE sessions are different issues. Oracle provides separate mechanisms to automate the cleanup of each.

The difference between dead connection and inactive conversation ,oracle Provides a separation mechanism to automatically clean up .

 

(1) Dead connections: Dead connection

 

    These are previously valid connections with the database but the  connection between the client and server processes has terminated   abnormally.

Some of the previous legal connections , However, due to the abnormal interruption of client and server processes .

 

    Examples of a dead connection:

 

    - A user reboots/turns-off their machine without logging off  or disconnecting from the database.

    - A network problem prevents communication between the client   and the server.

    1. Shut down the machine without logging off .

    2. The network is connected between client and server terminals .

 

    In these cases, the shadow process running on the server and the session in the database may not terminate. To automate the cleanup  of these sessions, you can use the Dead Connection Detection (DCD)   feature of Net8.

   In this case , The background process runs on the server side , The session will not be interrupted on the database side .

 

    When DCD is enabled, Net8 (server-side) sends a packet to the client.  If the client is active, the packet is discarded. If the client has  terminated, the server will receive an error and Net8 (server-side)  will end that session.

When the dead link starts ,NET8( Server side ) Will send a packet to the client . If the client is active , The bag was thrown away . If the client has been interrupted , The server will receive an error , The session will be interrupted .SQLNET.EXPIRE_TIME.

  

Refer to Note:151972.1: Dead Connection Detection (DCD) Explained,  for details regarding DCD.

 

 

(2) INACTIVE Sessions:

 

    These are sessions that remain connected to the database with a  status in v$session of INACTIVE.

The session remains connected to the server , But the state is inactive.

    Example of an INACTIVE session:

 

    - A user starts a program/session, then leaves it running and idle for an extended period of time.

    The user starts a session , After running for a while , Keep free for quite a while .

    To automate cleanup of INACTIVE sessions you can create a profile  with an appropriate IDLE_TIME setting and assign that profile to  the users.

   Automatically clean up inactive sessions , You can create a profile. And then set the right IDLE_TIME, Assigned to a specified user .

    Note:159978.1: How To Automate Disconnection of Idle Sessions,  outlines the steps to setup IDLE_TIME for this.

 

One .3.4  kill session Related content

kill session  yes DBA One of the things that happens all the time . If kill  It's not supposed to be kill  Of session, It's destructive , So try to avoid such mistakes as far as possible . At the same time, we should also pay attention to , If kill  Of session Belong to Oracle  Background processes , It is easy to cause database instance downtime .

One 、 Get what you need kill session Information about

 

SET LINESIZE 180

COLUMN spid FORMAT A10

COLUMN username FORMAT A10

COLUMN program FORMAT A40

 

SELECT s.inst_id,

         s.sid,

         s.serial#,

         p.spid,

         s.username,

         s.program,

         s.paddr,

         s.STATUS

FROM   gv$session s

JOIN gv$process p

ON p.addr = s.paddr

AND p.inst_id = s.inst_id

WHERE  s.type != 'BACKGROUND';

 

   INST_ID        SID    SERIAL# SPID       USERNAME   PROGRAM                                  PADDR            STATUS

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

         1        125          5 14029      SYS        sqlplus@orcltest (TNS V1-V3)             0000000077C98660 INACTIVE

         1          9         15 14274      SYS        sqlplus@orcltest (TNS V1-V3)             0000000077C99710 INACTIVE

         1         17          5 14078      LHR        sqlplus.exe                              0000000077CA5F50 INACTIVE

         1        144         31 14645      SYS        sqlplus@orcltest (TNS V1-V3)             0000000077CA7000 ACTIVE

         1         20          7 14647      SYS        plsqldev32.exe                           0000000077CA80B0 INACTIVE

         1        145         23 14651      SYS        plsqldev32.exe                           0000000077CA9160 INACTIVE

 

Two 、 Use ALTER SYSTEM KILL SESSION  Command implementation

   grammar :

      SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';

      SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

   

     about RAC In the environment kill session , We need to figure it out. We need to kill  Of session  At which node , You can query GV$SESSION View gets .11g Kill a session in a cluster environment :

alter system kill session'1228,42549,@ Instance number ';

for example :alter system kill session '1228, 42549, @2';

10g You should log in to a specific instance .

Kill session  Orders don't actually kill session, For example, waiting for the response of the remote database or rolling back the transaction , that session  It won't be immediately kill It must wait for the end of the current operation to execute , under these circumstances ,session  Will be marked as killed  state .

 

       We can do it in kill  Add... To the command immediate, The grammar is as follows :

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

 

This command doesn't affect performance , But it will immediately return to the current session, Handle kill operation , Instead of waiting for other information to complete .  If session  Always in killed  state , Then consider at the operating system level kill Lose the relevant process . But before the operation , Make sure that session  Are you executing rollback  operation . You can use the following SQL  To confirm .

SET LINESIZE 200

COLUMN username FORMAT A15

SELECT s.username,

      s.sid,

      s.serial#,

      t.used_ublk,

      t.used_urec,

      rs.segment_name,

      r.rssize,

      r.status

FROM v$transaction t,

      v$session s,

      v$rollstat r,

      dba_rollback_segs rs

WHERE s.saddr = t.ses_addr

AND   t.xidusn = r.usn

AND   rs.segment_id = t.xidusn

ORDER BY t.used_ublk DESC;

If we have our session, Then we have to wait rollback  To complete , And then at the operating system level kill session.

kill session  It's just killing the conversation . In some cases , Because of larger transactions or need to run longer SQL Statement will result in the need for kill Of session It doesn't kill right away . In this case, we will receive "marked for kill" Tips ( as follows ), Once the current transaction or session is completed , The conversation was immediately killed .

    alter system kill session '4730,39171'

    *

    ERROR at line 1:

    ORA-00031: session marked for kill

  The session will be killed in the following operation 146,144

    sys@AUSTIN> alter system kill session '146,23';

  

    System altered.

  

    sys@AUSTIN> alter system kill session '144,42';

  

    System altered.

  

    sys@AUSTIN> select inst_id,saddr,sid,serial#,paddr,username,status,program from gv$session where username is not null;

  

       INST_ID SADDR           SID    SERIAL# PADDR    USERNAME   STATUS   PROGRAM

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

             1 4C70BF04        144         42 4C6545A0 SCOTT      KILLED   sqlplus@oracle10g (TNS V1-V3)

             1 4C70E6B4        146         23 4C6545A0 TEST       KILLED   sqlplus@oracle10g (TNS V1-V3)

             1 4C71FC84        160         17 4C624174 SYS        ACTIVE   sqlplus@oracle10g (TNS V1-V3)

 

SQL> select sid,serial#,server,status from v$session where sid=22;

 

       SID    SERIAL# SERVER    STATUS

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

        22          7 PSEUDO    KILLED              

Be careful : In the query, you can see the PADDR The address has changed , Refer to the red font in the query results . If more than one session By kill  fall , Multiple session Of PADDR Changed to the same process address , The killed conversation is server The column into a PSEUDO.

One .3.4.1  oracle killed The problem of session not releasing

 

In general , While killing a conversation , Direct execution alter system kill session ‘sid,serial#’;

Administrator's Guide say , When session yes active When ,alter system kill session  Just to session The status of is marked as killed,server Turn into pseudo state , It doesn't release session Resources held , So we're at the end of the execution alter system kill session  after , The conversation still exists .

In this case, you can use immediate Options , Force immediately Kill conversation , as follows :

SQL> alter system kill session '3964,51752' immediate;

  SQL Language Reference(http://docs.oracle.com/cd/B28359_01/server.111/b28286/toc.htm#BEGIN)

In the Immediate The explanation is :IMMEDIATE Specify IMMEDIATE to instruct Oracle

Database to roll back ongoing transactions, release all session locks, recover the entire session state,and return control to you immediately.

One .3.4.2  killed How to find the state of the session spid Column

In general, the background process of our query session is as follows SQL, That is, through v$session Of paddr Column Association v$process Of addr Column , however killed State of v$session Of paddr The columns are all the same , So there is no way to pass the following SQL Go and find out .

SELECT b.SID,

       b.SERIAL# ,

       c.SPID,

       b.status

FROM   v$session b ,

       v$process c

WHERE  b.PADDR=c.ADDR

AND   b.sid=???;

 

If the conversation is already v$sesion Is in the killed state , So we're going through the above SQL I can't find out spid, You can use the following SQL Find out SPID:

 

----- Method 1

select spid, program from v$process

    where program!= 'PSEUDO'

    and addr not in (select paddr from v$session)

    and addr not in (select paddr from v$bgprocess)

    and addr not in (select paddr from v$shared_server);

 

select INST_ID, spid, program,'kill -9 '|| spid  kill9

  from gv$process a

where program != 'PSEUDO'

   and (INST_ID, addr) not in (select INST_ID, paddr from gv$session)

   and (INST_ID, addr) not in (select INST_ID, paddr from gv$bgprocess)

   and (INST_ID, addr) not in (select INST_ID, paddr from gv$shared_server)

   and a.PNAME is null;

 

----- Method 2  My own common method

set line 9999

col sessionid format a20

col sessionid_killed format a20

col kill_session format a60

 

SELECT a.INST_ID,

       a.SID || ',' || a.SERIAL# || ',' ||

       (select spid

          from gv$process b

         where b.INST_ID = a.INST_ID

           and A.creator_addr = b.ADDR --and decode(a.status,'KILLED',A.creator_addr,A.PADDR) = b.ADDR

        ) sessionid,

       a.PADDR,

       a.STATUS,

       a.PROGRAM,

       'alter system disconnect session ''' || sid || ',' || serial# || ''' immediate;' kill_session

  FROM gv$session a

WHERE a.USERNAME = 'SYS'

   and a.STATUS = 'KILLED';

As a result of the bug, 2 additional columns have been added to V$SESSION from 11g on:

V$SESSION

CREATOR_ADDR - state object address of creating process

CREATOR_SERIAL# - serial number of creating process

CREATOR_ADDR is the column that can be joined with the ADDR column in V$PROCESS to uniquely identify the killed process corresponding to the former session.

Following the previous example, this would identify the killed session

 

 

----- Method 3

SELECT a.SID || ',' || a.SERIAL# || ',' ||

       (select spid

          from gv$process b

         where b.INST_ID = a.INST_ID

           and A.pid = b.pid) sessionid,

       'alter system kill session ''' || sid || ',' || serial# || ''';' kill_session

  FROM gV$DETACHED_SESSION a;

 

 

----- Method 4

SELECT INST_ID, spid, program, 'kill -9 ' || spid kill9

  FROM gv$process a

WHERE (a.INST_ID, a.addr) in (select INST_ID, p.addr

                                 from gv$process p

                                where pid <> 1

                               minus

                               select INST_ID, s.paddr

                                 from gv$session s)

    and a.PNAME is null;

 

----- Method 4

SELECT s.SID, s.username,s.status,

x.ADDR,x.KSLLAPSC,x.KSLLAPSN,x.KSLLASPO,x.KSLLID1R,x.KSLLRTYP,

decode(bitand(x.ksuprflg,2),0,null,1)

FROM xksuprx,vksuprx,vsession s

WHERE s.paddr(+)=x.addr

and bitand(ksspaflg,1)!=0 

 

One .3.4.3   why killed The state of the process has been v$session You can find ?

 

[ID 100859.1] It is mentioned in this article that pmon How to clean up killed Conversation :

PMON will not delete the session object itself until the client connected to

that session notices that it has been killed.  Therefore, the sequence of

events is:

      

1) alter system kill session is issued - the STATUS of the session object in

   V$SESSION becomes KILLED, its server becomes PSEUDO.

      

2) PMON cleans up the *resources* allocated to the session

   (i.e., rolls back its transaction, releases its locks, etc).

      

3) the entry in V$SESSION remains there until the client of that session (the

   client is the process associated with the OSUSER,MACHINE,PROCESS columns in

   the V$SESSION view) tries to do another request.

      

4) the client attempts another SQL statement and gets back ORA-28.

      

5) PMON can now remove the entry from V$SESSION.

   This behavior is necessary because the client still has pointers to the

   session object even though the session has been killed.  Therefore, the

   object cannot be deleted until the client is no longer pointing at it.

 

alter system kill session after , The conversation state changes to killed,pmon After recycling resources , The information of the conversation remains in v$session in , Until the client requests again , Database return ORA-28 error :"your session has been killed".

here pmon Only from the v$session Remove information from these sessions . And in the above , Client machine restart , No more requests can be sent to the database , So from v$session You can always find , The only way to kill a process is through the operating system , To trigger pmon from v$session Middle clearance .

then Oracle Just wait PMON To get rid of these Session. So usually waiting for one to be marked as Killed Of Session It takes a long time to exit . If at this time Kill Of process, Try the task again , Then you will be prompted to interrupt the process immediately ,process sign out , here Oracle It will start immediately PMON To clear the session. This is handled as an exception interrupt .

One .3.4.4   why kill session after ,paddr It's the same value ?

[ID 387077.1] It is mentioned in this article that :

Bug 5453737 WHEN A SESSION IS KILLED, PADDR CHANGES IN VSESSIONBUTADDRNOTINVSESSIONBUTADDRNOTINVPROCESS closed as not a bug with the following explanation:

When a session is killed, the session state object(and all the child state objects under the session state object) move out from under the original parent process state object, and are placed under the pseudo process state object (which is expected, given the parent/child process mechanism on Unix). PMON will clean up all the state objects found under the pseudo process state object. That explains why PADDR changes in VSESSIONwhenasessioniskilled.NewPADDRyouareseeinginvSESSIONwhenasessioniskilled.NewPADDRyouareseeinginvSESSION is the address of the pseudo process state object. This shows up in system state under PSEUDO PROCESS for group DEFAULT: V$PROCESS still maintains the record of the original parent process. This is expected.

 

Use the following sql lookup killed State of spid:

select spid, program from v$process

    where program!= 'PSEUDO'

    and addr not in (select paddr from v$session)

    and addr not in (select paddr from v$bgprocess)

    and addr not in (select paddr from v$shared_server);

 

Through the underlying table x$ksupr relation v$session It's fine too ,11.1.0.6 Version and higher in v$session Added 2 A field CREATOR_ADDR and CREATOR_SERIAL# Used to mark this situation . Then you can use the operating system command kill -9 To kill these processes .

 

One .3.4.5  ALTER SYSTEM KILL SESSION  Insufficient authority

You need to give permission ,dba Characters don't have to ,grant alter system to lhr;

wpsCF07.tmp 

 

 

 

One .3.4.6   Grant ordinary users to kill themselves session Authority

If there is no dba Characters or not alter system permissions , Business users can't kill their own conversations , thus , Can only dba Come in , In fact, we can use this function through the following SQL Script to complete .

-------  user replace XXXXXX

-------   Ordinary users can kill Their own session  Give app users permission to kill themselves session Authority

create or replace view vw_myownersession_lhr

as

select * from v$session where username = USER;

 

create or replace public synonym syn_myownersession_lhr for sys.vw_myownersession_lhr;

 

 

create or replace procedure pro_kill_myown_session_lhr( p_sid in varchar2,p_serial# in varchar2)

is

    cursor_name     pls_integer default dbms_sql.open_cursor;

    ignore          pls_integer;

BEGIN

    select count(*) into ignore

      from v$session

     where username = USER

       and sid = p_sid

       and serial# = p_serial# ;

 

    if ( ignore = 1 )

    then

        dbms_sql.parse(cursor_name,'alter system disconnect session '''||p_sid||','||p_serial#||''' immediate',dbms_sql.native);

        ignore := dbms_sql.execute(cursor_name);

    else

        raise_application_error( -20001,'You do not own session ''' ||p_sid || ',' || p_serial# ||'''' );

    end if;

END pro_kill_myown_session_lhr;

/

 

create or replace public synonym pro_kill_session_lhr for sys.pro_kill_myown_session_lhr;

 

grant select on syn_myownersession_lhr to XXXXXX;

grant execute on pro_kill_session_lhr to XXXXXX;

 

SELECT USERENV('SID') FROM DUAL;

select sid,serial#,paddr,status from syn_myownersession_lhr where sid=19;

exec pro_kill_session_lhr(19,15);

 

One .3.4.7  ALTER SYSTEM DISCONNECT SESSION  explain

Alter system disconnect session It's an optional kill session  Methods . And kill session  Different commands ,disconnect session  Orders will kill  fall  dedicated server process,  This command is equivalent to at the operating system level kill  fall server process.

 

The specific syntax is as follows :

SQL> ALTER SYSTEM DISCONNECT SESSION'sid,serial#' POST_TRANSACTION;

SQL> ALTER SYSTEM DISCONNECT SESSION'sid,serial#' IMMEDIATE;

 

POST_TRANSACTION  Option will wait for the transaction to complete before disconnecting .

IMMEDIATE Option immediately disconnects , Then the transaction goes on recover operation .

 

this 2 The two options can also be used together , But one of them must be specified , Otherwise you will report an error :

SQL> alter system disconnect session'30,7';

alter system disconnect session '30,7'

                                     *

ERROR at line 1:

ORA-02000: missing POST_TRANSACTION orIMMEDIATE keyword

SQL>

 

SQL> alter system disconnect session'15,12' post_transaction immediate;

System altered.

     

Use alter system disconnectsession  The command doesn't need to switch to the system kill session, It also reduces kill  The chance of a wrong process .

 

One .3.4.8  PMON  Clean up the interval

PMON The process is responsible for handling the release of resources related to the abnormal ending process .PMON Periodically awakened , It can be done to "_PKT_PMON_INTERVAL" This is a hidden parameter to modify , The default is 50 second . You can also find the process's PID, And then in oradebug in , Carry out orders oradebug wakeup orapid(oracle Process PID, No OS Of PID) To wake up manually PMON process . It can be used alter session set events '100246 trace name conext forever,level 4' Check it out. PMON Related operations of .

SYS@LHRDGZK1> SELECT a.INDX,

  2         a.KSPPINM NAME,

  3         a.KSPPDESC,

  4         b.KSPPSTVL

  5  FROM   x$ksppi  a,

  6         x$ksppcv b

  7  WHERE  a.INDX = b.INDX

  8  and lower(a.KSPPINM) like  lower('%¶meter%');

Enter value for parameter: _PKT_PMON_INTERVAL

old   8: and lower(a.KSPPINM) like  lower('%¶meter%')

new   8: and lower(a.KSPPINM) like  lower('%_PKT_PMON_INTERVAL%')

 

INDX NAME                   KSPPDESC                             KSPPSTVL

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

   61 _pkt_pmon_interval     PMON process clean-up interval (cs)  50

 

 

alter system set "_PKT_PMON_INTERVAL"=5;

 

One .3.5  MOS Some information on

wpsCF08.tmpwpsCF09.tmpwpsCF19.tmp

wpsCF1A.tmp

wpsCF1B.tmpwpsCF1C.tmpwpsCF1D.tmpwpsCF1E.tmp

wpsCF1F.tmpwpsCF20.tmpwpsCF21.tmpwpsCF32.tmp

therefore metalink and google To the following information :

Removing Sessions in Killed Status on Unix [ID 274216.1]

ALTER SYSTEM KILL Session Marked for Killed Forever [ID 1020720.102]

KILLING INACTIVE SESSIONS DOES NOT REMOVE SESSION ROW FROM V$SESSION [ID 1041427.6]

ALTER SYSTEM KILL SESSION does not Release Locks Killing a Thread on Windows NT [ID 100859.1]

 

http://blog.csdn.net/tianlesoftware/article/details/7417058

http://www.eygle.com/faq/Kill_Session.htm

 

 

--- More useful

How To Find The Process Identifier (pid, spid) After The Corresponding Session Is Killed? [ID 387077.1]

HOW TO HAVE ORACLE CLEAN-UP OLD USER INFO AFTER KILLING SESSION UNDER MTS Note:1023442.6

 

 

 

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

 

 

Chapter two   Experimental part

Two .1   Introduction to the experimental environment

project

primary db

db  type

Single instance

db version

11.2.0.3.0

db  Storage

ASM

host IP Address /hosts To configure

192.168.59.129

OS Version and kernel edition

rhel 6.5

 

 

Two .2   Experimental content

Experiment number

Experimental content

1

Set user profile Of idle_time  Parameters

2

kill session When to add immediate And no more

3

To ordinary users kill The user's own rights

4

KILLED How to find the relevant state of the conversation SPID

 

 

Two .3   Experimental process

 

Two .3.1   Set user profile Of idle_time  Parameters

You can refer to :

http://blog.csdn.net/leshami/article/details/9184917

http://blog.csdn.net/tianlesoftware/article/details/6238279

 

Set up resource_limit  by true, This parameter defaults to false.

[oracle@orcltest ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jun 26 17:55:53 2016

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> show parameter RESOURCE_LIMIT

 

NAME                                 TYPE        VALUE

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

resource_limit                       boolean     FALSE

SQL> alter system set resource_limit=true;

 

System altered.

 

 

Create... For business users profile file . Then assign the configuration file to the business user .

 

SQL> create profile pro_lhr limit idle_time 1;

 

Profile created.

 

SQL> alter user lhr profile pro_lhr;

 

User altered.

 

SQL>

 

SQL> set line 9999 pagesize 9999

SQL> select * from dba_profiles where profile='PRO_LHR';

 

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT

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

PRO_LHR                        COMPOSITE_LIMIT                  KERNEL   DEFAULT

PRO_LHR                        SESSIONS_PER_USER                KERNEL   DEFAULT

PRO_LHR                        CPU_PER_SESSION                  KERNEL   DEFAULT

PRO_LHR                        CPU_PER_CALL                     KERNEL   DEFAULT

PRO_LHR                        LOGICAL_READS_PER_SESSION        KERNEL   DEFAULT

PRO_LHR                        LOGICAL_READS_PER_CALL           KERNEL   DEFAULT

PRO_LHR                        IDLE_TIME                        KERNEL   1

PRO_LHR                        CONNECT_TIME                     KERNEL   DEFAULT

PRO_LHR                        PRIVATE_SGA                      KERNEL   DEFAULT

PRO_LHR                        FAILED_LOGIN_ATTEMPTS            PASSWORD DEFAULT

PRO_LHR                        PASSWORD_LIFE_TIME               PASSWORD DEFAULT

PRO_LHR                        PASSWORD_REUSE_TIME              PASSWORD DEFAULT

PRO_LHR                        PASSWORD_REUSE_MAX               PASSWORD DEFAULT

PRO_LHR                        PASSWORD_VERIFY_FUNCTION         PASSWORD DEFAULT

PRO_LHR                        PASSWORD_LOCK_TIME               PASSWORD DEFAULT

PRO_LHR                        PASSWORD_GRACE_TIME              PASSWORD DEFAULT

 

16 rows selected.

 

SQL>

 

Start a session , Wait a minute

D:\Users\xiaomaimiao>sqlplus lhr/lhr@192.168.59.129/oratest

 

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jun 26 18:03:09 2016

 

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

LHR@192.168.59.129/oratest> set time on

18:03:12 LHR@192.168.59.129/oratest> SELECT a.SID,

18:03:37   2         b.SERIAL# ,

18:03:37   3         c.SPID,

18:03:37   4         b.status

18:03:37   5  FROM   v$mystat  a,

18:03:37   6         v$session b ,

18:03:37   7         v$process c

18:03:37   8  WHERE  a.SID = b.SID

18:03:37   9  and b.PADDR=c.ADDR

18:03:37  10  AND    rownum = 1;

 

       SID    SERIAL# SPID                     STATUS

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

       19          9 14689                    ACTIVE

 

 

1 Query in other session windows in minutes :

SQL> SELECT b.SID,

  2         b.SERIAL# ,

  3         c.SPID,

  4         b.status

  5  FROM   v$session b ,

  6         v$process c

  7  WHERE   b.PADDR=c.ADDR

  8  AND    b.sid=19;

 

       SID    SERIAL# SPID                     STATUS

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

        19          9 14689                    INACTIVE

 

SQL> /

 

       SID    SERIAL# SPID                     STATUS

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

        19          9 14689                    SNIPED

 

SQL>

 

SQL> alter system kill session '19,9' immediate;

 

System altered.

 

SQL> SELECT b.SID,

  2         b.SERIAL# ,

  3         c.SPID,

  4         b.status

  5  FROM   v$session b ,

  6         v$process c

  7  WHERE   b.PADDR=c.ADDR

  8  AND    b.sid=19;

 

no rows selected

 

SQL>

 

 

Two .3.2   Grant ordinary users to kill themselves session Authority

 

Create a normal user lhrtest, grant resource and connect jurisdiction .

SQL> create user lhrtest identified by lhrtest;

 

User created.

 

SQL> grant resource ,connect to lhrtest;

 

Grant succeeded.

 

 

 

Use sys Users to create the required scripts :

SQL> create or replace view vw_myownersession_lhr

  2  as

  3  select * from v$session where username = USER;

 

View created.

 

SQL> create or replace public synonym syn_myownersession_lhr for sys.vw_myownersession_lhr;

 

Synonym created.

 

SQL> create or replace procedure pro_kill_myown_session_lhr( p_sid in varchar2,p_serial# in varchar2)

  2  is

  3      cursor_name     pls_integer default dbms_sql.open_cursor;

  4      ignore          pls_integer;

  5  BEGIN

  6      select count(*) into ignore

  7        from v$session

  8       where username = USER

  9         and sid = p_sid

10         and serial# = p_serial# ;

11 

12      if ( ignore = 1 )

13      then

14          dbms_sql.parse(cursor_name,'alter system disconnect session '''||p_sid||','||p_serial#||''' immediate',dbms_sql.native);

15          ignore := dbms_sql.execute(cursor_name);

16      else

17          raise_application_error( -20001,'You do not own session ''' ||p_sid || ',' || p_serial# ||'''' );

18      end if;

19  END pro_kill_myown_session_lhr;

20  /

 

Procedure created.

 

SQL> create or replace public synonym pro_kill_session_lhr for sys.pro_kill_myown_session_lhr;

 

Synonym created.

 

SQL> grant select on syn_myownersession_lhr to lhrtest;

 

Grant succeeded.

 

SQL> grant execute on pro_kill_session_lhr to lhrtest;

 

Grant succeeded.

 

SQL>

 

 

use windows The client logs in to a session :

D:\Users\xiaomaimiao>sqlplus lhrtest/lhrtest@192.168.59.129/oratest

 

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jun 26 19:19:42 2016

 

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

LHRTEST@192.168.59.129/oratest> SELECT USERENV('SID') FROM DUAL;

 

USERENV('SID')

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

            19

 

LHRTEST@192.168.59.129/oratest> select sid,serial#,paddr,status from syn_myownersession_lhr where sid=19;

 

       SID    SERIAL# PADDR            STATUS

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

        19         15 0000000077C9B870 ACTIVE

 

Do not shut down. 19,15 window , Then reopen a session window :

 

SQL> conn lhrtest/lhrtest

Connected.

SQL> show user

USER is "LHRTEST"

SQL> select sid,serial#,paddr,status from syn_myownersession_lhr where sid=19;

 

       SID    SERIAL# PADDR            STATUS

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

        19         15 0000000077C9B870 INACTIVE

 

SQL> alter system kill session '19,15' immediate;

alter system kill session '19,15' immediate

*

ERROR at line 1:

ORA-01031: insufficient privileges

 

 

SQL>  exec pro_kill_session_lhr(19,15);

 

PL/SQL procedure successfully completed.

 

SQL> select sid,serial#,paddr,status from syn_myownersession_lhr where sid=19;

 

no rows selected

 

SQL>

 

You can see that ordinary users can also kill their own user's session .

 

 

Two .3.3  kill session When to add immediate And no addition immediate The difference between

 

kill session  Test of :

SQL> set line 9999

SQL> col sessionid format a20

SQL> col sessionid_killed format a20

SQL> col kill_session format a60

SQL>

SQL> SELECT a.INST_ID,

  2         a.SID || ',' || a.SERIAL# || ',' ||

  3         (select spid

  4            from gv$process b

  5           where b.INST_ID = a.INST_ID

  6             and A.creator_addr = b.ADDR --and decode(a.status,'KILLED',A.creator_addr,A.PADDR) = b.ADDR

  7          ) sessionid,

  8         a.PADDR,

  9         a.STATUS,

10         a.PROGRAM,

11         a.server,

12         'alter system disconnect session ''' || sid || ',' || serial# || ''' immediate;' kill_session

13    FROM gv$session a

14   WHERE a.type != 'BACKGROUND';

 

   INST_ID SESSIONID            PADDR            STATUS   PROGRAM                                  SERVER    KILL_SESSION

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

         1 9,169,14901          0000000077C9B870 INACTIVE sqlplus.exe                              DEDICATED alter system disconnect session '9,169' immediate;

         1 20,9,14891           0000000077CA5F50 INACTIVE plsqldev32.exe                           DEDICATED alter system disconnect session '20,9' immediate;

         1 23,35,14885          0000000077C99710 INACTIVE sqlplus.exe                              DEDICATED alter system disconnect session '23,35' immediate;

         1 125,7,14873          0000000077C98660 ACTIVE   sqlplus@orcltest (TNS V1-V3)             DEDICATED alter system disconnect session '125,7' immediate;

         1 145,23,14651         0000000077CA9160 INACTIVE plsqldev32.exe                           DEDICATED alter system disconnect session '145,23' immediate;

 

SQL> alter system kill session '9,169';

 

System altered.

 

SQL> alter system kill session '20,9';

 

System altered.

 

SQL> set line 9999

SQL> col sessionid format a20

SQL> col sessionid_killed format a20

SQL> col kill_session format a60

SQL>

SQL> SELECT a.INST_ID,

  2         a.SID || ',' || a.SERIAL# || ',' ||

  3         (select spid

  4            from gv$process b

  5           where b.INST_ID = a.INST_ID

  6             and A.creator_addr = b.ADDR --and decode(a.status,'KILLED',A.creator_addr,A.PADDR) = b.ADDR

  7          ) sessionid,

  8         a.PADDR,

  9         a.STATUS,

10         a.PROGRAM,

11         a.server,

12         'alter system disconnect session ''' || sid || ',' || serial# || ''' immediate;' kill_session

13    FROM gv$session a

14   WHERE a.type != 'BACKGROUND';

 

   INST_ID SESSIONID            PADDR            STATUS   PROGRAM                                  SERVER    KILL_SESSION

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

         1 9,169,14901          0000000077D2DCF8 KILLED   sqlplus.exe                              PSEUDO    alter system disconnect session '9,169' immediate;

         1 20,9,14891           0000000077D2DCF8 KILLED   plsqldev32.exe                           PSEUDO    alter system disconnect session '20,9' immediate;

         1 23,35,14885          0000000077C99710 INACTIVE sqlplus.exe                              DEDICATED alter system disconnect session '23,35' immediate;

         1 125,7,14873          0000000077C98660 ACTIVE   sqlplus@orcltest (TNS V1-V3)             DEDICATED alter system disconnect session '125,7' immediate;

         1 145,23,14651         0000000077CA9160 INACTIVE plsqldev32.exe                           DEDICATED alter system disconnect session '145,23' immediate;

 

SQL>

 

You can see , Killed 2 A conversation in v$session You can find it in all of them , It's just ① Its status Change into KILLED,②server The column becomes PSEUDO,③paddr The columns are all the same , So if you still use it at this time paddr Column process Column must not be found spid The no. , At this time, we can use the above several SQL To query the conversation spid, And then use kill -9 Kill the conversation .

We use it kill -9 Kill the conversation , Waiting backstage PMON To automatically clean up the process , if PMON Very slowly , We can use it manually oradebug wakeup 2 Wake up PMON process , You can also set PMON Clean up interval for alter system set "_PKT_PMON_INTERVAL"=5;:

 

[oracle@orcltest ~]$ ps -ef|grep 14901

oracle   14901     1  0 19:51 ?        00:00:00 oracleoratest (LOCAL=NO)

oracle   14959 14625  0 20:17 pts/8    00:00:00 grep 14901

[oracle@orcltest ~]$ kill -9 14901

[oracle@orcltest ~]$ ps -ef|grep 14901

oracle   14961 14625  0 20:17 pts/8    00:00:00 grep 14901

[oracle@orcltest ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jun 26 20:17:54 2016

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

 

SQL> set line 9999

SQL> col sessionid format a20

SQL> col sessionid_killed format a20

SQL> col kill_session format a60

SQL>

SQL> SELECT a.INST_ID,

  2         a.SID || ',' || a.SERIAL# || ',' ||

  3         (select spid

  4            from gv$process b

  5           where b.INST_ID = a.INST_ID

  6             and A.creator_addr = b.ADDR --and decode(a.status,'KILLED',A.creator_addr,A.PADDR) = b.ADDR

  7          ) sessionid,

  8         a.PADDR,

  9         a.STATUS,

10         a.PROGRAM,

11         a.server,

12         'alter system disconnect session ''' || sid || ',' || serial# || ''' immediate;' kill_session

13    FROM gv$session a

14   WHERE a.type != 'BACKGROUND';

 

   INST_ID SESSIONID            PADDR            STATUS   PROGRAM                                          SERVER    KILL_SESSION

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

         1 9,169,14901          0000000077D2DCF8 KILLED   sqlplus.exe                                      PSEUDO    alter system disconnect session '9,169' immediate;

         1 20,9,14891           0000000077D2DCF8 KILLED   plsqldev32.exe                                   PSEUDO    alter system disconnect session '20,9' immediate;

         1 23,35,14885          0000000077C99710 INACTIVE sqlplus.exe                                      DEDICATED alter system disconnect session '23,35' immediate;

         1 125,9,14964          0000000077C98660 ACTIVE   sqlplus@orcltest (TNS V1-V3)                     DEDICATED alter system disconnect session '125,9' immediate;

 

 

SQL> select INST_ID, spid, program,A.PNAME,A.PID

  2    from gv$process a

  3  where a.PNAME='PMON';

 

   INST_ID SPID       PROGRAM                                  PNAME        PID

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

         1 13955      oracle@orcltest (PMON)                   PMON           2

 

SQL> oradebug wakeup 2

Statement processed.

SQL>

SQL> SET LINESIZE 180

SQL> COLUMN spid FORMAT A10

SQL> COLUMN username FORMAT A10

SQL> COLUMN program FORMAT A40

SQL> SELECT s.inst_id,

  2         s.sid,

  3         s.serial#,

  4         p.spid,

  5         s.username,

  6         s.program,

  7         s.paddr,

  8         s.STATUS,

  9   s.server

10    FROM gv$session s

11    left outer JOIN gv$process p

12      ON p.addr = s.paddr

13     AND p.inst_id = s.inst_id

14   WHERE s.type != 'BACKGROUND';

 

   INST_ID        SID    SERIAL# SPID       USERNAME   PROGRAM                                  PADDR            STATUS   SERVER

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

         1        125          9 14964      SYS        sqlplus@orcltest (TNS V1-V3)             0000000077C98660 ACTIVE   DEDICATED

         1         23         35 14885      SYS        sqlplus.exe                              0000000077C99710 INACTIVE DEDICATED

         1         20         11 14966      SYS        plsqldev32.exe                           0000000077C9B870 INACTIVE DEDICATED

         1          9        177 14968      SYS        plsqldev32.exe                           0000000077CA5F50 INACTIVE DEDICATED

 

SQL>

 

kill session immediate Test of :

SQL> set line 9999

SQL> col sessionid format a20

SQL> col sessionid_killed format a20

SQL> col kill_session format a60

SQL>

SQL> SELECT a.INST_ID,

  2         a.SID || ',' || a.SERIAL# || ',' ||

  3         (select spid

  4            from gv$process b

  5           where b.INST_ID = a.INST_ID

  6             and A.creator_addr = b.ADDR --and decode(a.status,'KILLED',A.creator_addr,A.PADDR) = b.ADDR

  7          ) sessionid,

  8         a.PADDR,

  9         a.STATUS,

10         a.PROGRAM,

11         a.server,

12         'alter system disconnect session ''' || sid || ',' || serial# || ''' immediate;' kill_session

13    FROM gv$session a

14   WHERE a.type != 'BACKGROUND';

 

   INST_ID SESSIONID            PADDR            STATUS   PROGRAM                                  SERVER    KILL_SESSION

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

         1 9,169,14901          0000000077D2DCF8 KILLED   sqlplus.exe                              PSEUDO    alter system disconnect session '9,169' immediate;

         1 20,9,14891           0000000077D2DCF8 KILLED   plsqldev32.exe                           PSEUDO    alter system disconnect session '20,9' immediate;

         1 23,35,14885          0000000077C99710 INACTIVE sqlplus.exe                              DEDICATED alter system disconnect session '23,35' immediate;

         1 125,7,14873          0000000077C98660 ACTIVE   sqlplus@orcltest (TNS V1-V3)             DEDICATED alter system disconnect session '125,7' immediate;

         1 145,23,14651         0000000077CA9160 INACTIVE plsqldev32.exe                           DEDICATED alter system disconnect session '145,23' immediate;

 

SQL> alter system kill session '145,23' immediate;

 

System altered.

 

SQL> set line 9999

SQL> col sessionid format a20

SQL> col sessionid_killed format a20

SQL> col kill_session format a60

SQL>

SQL> SELECT a.INST_ID,

  2         a.SID || ',' || a.SERIAL# || ',' ||

  3         (select spid

  4            from gv$process b

  5           where b.INST_ID = a.INST_ID

  6             and A.creator_addr = b.ADDR --and decode(a.status,'KILLED',A.creator_addr,A.PADDR) = b.ADDR

  7          ) sessionid,

  8         a.PADDR,

  9         a.STATUS,

10         a.PROGRAM,

11         a.server,

12         'alter system disconnect session ''' || sid || ',' || serial# || ''' immediate;' kill_session

13    FROM gv$session a

14   WHERE a.type != 'BACKGROUND';

 

   INST_ID SESSIONID            PADDR            STATUS   PROGRAM                                  SERVER    KILL_SESSION

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

         1 9,169,14901          0000000077D2DCF8 KILLED   sqlplus.exe                              PSEUDO    alter system disconnect session '9,169' immediate;

         1 20,9,14891           0000000077D2DCF8 KILLED   plsqldev32.exe                           PSEUDO    alter system disconnect session '20,9' immediate;

         1 23,35,14885          0000000077C99710 INACTIVE sqlplus.exe                              DEDICATED alter system disconnect session '23,35' immediate;

         1 125,7,14873          0000000077C98660 ACTIVE   sqlplus@orcltest (TNS V1-V3)             DEDICATED alter system disconnect session '125,7' immediate;

 

SQL>

You can see that if you add immediate If the session has no transaction, it will be released immediately , And v$session The view is also cleaned up .