【Oracle】-【SNIPED和KILLED】-SPINED和KILLED的session清理流程

@dbsnake大拿的一個示例：

SQL> show parameter limit

NAME     TYPE

VALUE

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

asm_power_limit     integer

1

java_soft_sessionspace_limit     integer

resource_limit     boolean

TRUE

SQL> create profile idletime limit idle_time 1;

Profile created.

SQL> alter user bisal profile idletime;

User altered.

SQL> conn bisal/bisal

From another session:

SQL> select status from v$session where username='BISAL';

STATUS

--------

ACTIVE

INACTIVE

After one minute:

SNIPED

From BISAL session:

SQL> select * from dual;

select * from dual

*

ERROR at line 1:

ORA-02396: exceeded maximum idle time, please connect again

驗證一個SNIPED的session，重新執行一條SQL，Oracle會自動删除該session。

再做一個實驗：

Conn bisal

SQL> select username,serial#, sid, to_timestamp(logon_time) from v$session

  2  where username='BISAL';

USERNAME  SERIAL#

SID            TO_TIMESTAMP(LOGON_TIME)

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

BISAL 8

208                   10-JUL-13 12.00.00 AM

BISAL       15

215                   10-JUL-13 12.00.00 AM

SQL> alter system kill session '215, 15';

System altered.

KILLED

ORA-00028: your session has been killed

即KILLED狀态的session執行一個SQL，也會立即清除，這個據@eygle介紹是PMON負責的。"如果此時被Kill的process，重新嘗試執行任務，那麼馬上會收到程序中斷的提示，process退出，此時Oracle會立即啟動PMON來清除該session.這被作為一次異常中斷處理."