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

@dbsnake大拿的一个示例：

SQL> show parameter limit

NAME    TYPE VALUE

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

asm_power_limit     integer1

java_soft_sessionspace_limit    integer0

resource_limit    booleanTRUE

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)

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

BISAL8 208                   10-JUL-13 12.00.00 AM

BISAL      15215                   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.这被作为一次异常中断处理."