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

@dbsnake大拿的一個示例：

SQL> show parameter limit
NAME     TYPE  VALUE
------------------------------------ ----------- ------------------------------
asm_power_limit     integer 1
java_soft_sessionspace_limit     integer 0
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:

SQL> select status from v$session where username='BISAL';
STATUS
--------
ACTIVE
SNIPED

From BISAL session:

SQL> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-02396: exceeded maximum idle time, please connect again

From another session:

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

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

再做一個實驗：

Conn bisal

From another session:

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

STATUS
--------
ACTIVE
INACTIVE

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.

SQL> select status from v$session where username='BISAL';
STATUS
--------
ACTIVE
KILLED

From BISAL session:

SQL> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-00028: your session has been killed

From another session:

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

STATUS
--------
ACTIVE

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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7192724/viewspace-767163/，如需轉載，請註明出處，否則將追究法律責任。