ORACLE快速彻底Kill掉的会话

SQL> conn etl/etl

Connected.

SQL>  update test set status='invalid';

55944 rows updated.

SQL> update test2 set dropped='Y';

3090 rows updated.

SQL> show user

USER is "SYS"

SQL> update etl.test2 set dropped='Y';

3090 rows updated.

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         27      33353 000000025F1D1FC8 ETL                            INACTIVE

000000025C23A608         37      11448 000000025F1D27B0 SYS                            ACTIVE

000000025C24BC50         63      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         27      33353 000000025C21A0B0 ETL                            KILLED

000000025C23A608         37      11448 000000025F1D27B0 SYS                            ACTIVE

000000025C24BC50         63      54311 000000025F1D5F08 SYS                            INACTIVE

SQL> alter system kill session '63,54311';

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         27      33353 000000025C21A0B0 ETL                            KILLED

000000025C23A608         37      11448 000000025F1D27B0 SYS                            ACTIVE

000000025C24BC50         63      54311 000000025C21A0B0 SYS                            KILLED

SQL> select p.addr from v$process p where pid <> 1

  2  minus

  3  select s.paddr from v$session s;

ADDR

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

000000025F1D1FC8

000000025F1D5F08

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         27      33353 000000025C21A0B0 ETL                            KILLED

000000025C23A608         37      11448 000000025F1D27B0 SYS                            ACTIVE

000000025C24BC50         63      54311 000000025C21A0B0 SYS                            KILLED

SQL>  select addr, pid, spid, username from v$process where addr in ('000000025F1D1FC8','000000025F1D5F08');

ADDR                    PID SPID         USERNAME

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

000000025F1D1FC8         22 12959        oracle

000000025F1D5F08         38 12971        oracle

SQL> ! kill -9 12959

SQL> ! kill -9 12971

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

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

000000025C23A608         37      11448 000000025F1D27B0 SYS                            ACTIVE

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 have the database open. Your session and the session to be terminated must be 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 instance where the target session to be killed exists. You can find the instance ID by querying the GV$ tables.

If the session is performing some activity that must be completed, such as waiting for a reply from a remote database or rolling 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 be terminated and returns control to you with a message that the session is marked to be terminated. The PMON background process then marks the session as terminated when the activity is complete.

Whether or not the session has an 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 that the session has been terminated.

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';