[Oracle]-[SNIPED and KILLED]-session cleaning process for SPINED and KILLED

[Oracle]-[SNIPED and KILLED]-Example of the session cleaning process @ dbsnake danale for the SPINED and KILLED:

SQL> show parameter limitNAME     TYPE  VALUE------------------------------------ ----------- ------------------------------asm_power_limit     integer1java_soft_sessionspace_limit     integer0resource_limit     booleanTRUESQL> create profile idletime limit idle_time 1;Profile created.SQL> alter user bisal profile idletime;User altered.SQL> conn bisal/bisalFrom another session:SQL> select status from v$session where username='BISAL';STATUS--------ACTIVEINACTIVEAfter one minute:SQL> select status from v$session where username='BISAL';STATUS--------ACTIVESNIPEDFrom BISAL session:SQL> select * from dual;select * from dual*ERROR at line 1:ORA-02396: exceeded maximum idle time, please connect againFrom another session:SQL> select status from v$session where username='BISAL';STATUS--------ACTIVE

 

Verify an SNIPED session and run an SQL statement again. Oracle automatically deletes the session. Perform another experiment:

Conn bisalFrom another session:SQL> select status from v$session where username='BISAL';STATUS--------ACTIVEINACTIVESQL> 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 AMBISAL       15215                   10-JUL-13 12.00.00 AMSQL> alter system kill session '215, 15';System altered.SQL> select status from v$session where username='BISAL';STATUS--------ACTIVEKILLEDFrom BISAL session:SQL> select * from dual;select * from dual*ERROR at line 1:ORA-00028: your session has been killedFrom another session:SQL> select status from v$session where username='BISAL';STATUS--------ACTIVE

 

That is to say, sessions in the KILLED status execute an SQL statement and immediately clear the statement. According to @ eygle, PMON is responsible for this statement. "If the Kill process tries to execute the task again at this time, the system immediately receives a message indicating that the process is interrupted. The process exits, and Oracle immediately starts PMON to clear the session. this is handled as an exception interrupt."