Oracle regularly cleans up inactive sessions

Oracle database sessions have five states of active, INACTIVE, killed, CACHED, sniped. A inactive state session indicates that the session is inactive, idle, and waiting. For example, PL/SQL developer is connected to a database, and after executing a statement, the session is in the inactive state if the SQL statement is not resumed. In general, a small number of Inactvie sessions have no impact on the database, if due to some reasons such as program design causes the database to have a large number of sessions in inactive state, it will result in a large number of system resources are consumed, resulting in more sessions than the maximum system session, The ora-00018:maximum number of sessions exceeded error appears.

Sometimes it is necessary to clean up those conversations that have been in inactive state for a long time. It must be unrealistic to periodically check and kill such conversations, and to regularly clean up those long-inactive sessions, which can only be done through homework; It is also important to note that killing these sessions requires caution, and a little attention may kill some normal sessions. So how do we define this kind of conversation? Here's what I've defined in conjunction with business rules:

1: The status of the session must be inactive, if the session state is active, killed, CACHED, sniped state, do not consider.

2: The session must have been in the inactive state for a long time. For example, if you are in a inactive state that exceeds the two-hour session process, consider kill. Depending on the business or needs, it is possible to kill the session process in more than half an hour. As for how to calculate the time in the inactive session state, this can be identified by the V$session last_call_et field, you need to query the inactive state for two hours or more sessions, you can pass the query conditions S.last_call_et >= 60*60*2 Implementation, of course, preferably written s.last_call_et >= 7200

3: The program that connects to the session. For example, inactive sessions generated by a particular application are cleaned up. For example, the Toad tool, the PL/SQL developer tool. About program This needs to be set according to the current project situation, the following only uses TOAD.EXE, w3wp. EXE for illustrative purposes.

   1:select SID, Serial#,module, STATUS

   2:from v$session S

   3:where S.username is not NULL

   4:and UPPER (S.program) in (' TOAD. EXE ', ' w3wp. EXE ')

   5:and S.last_call_et >= 60*60*2

   6:and s.status = ' INACTIVE '

   7:order by SID DESC;

In the case of a RAC environment, it is best to use the following SQL statement, using the global view gv$session.

   1:select SID, serial#, inst_id, Module,status

   2:from gv$session S

   3:where S.username is not NULL

   4:and UPPER (S.program) in (' TOAD. EXE ', ' w3wp. EXE ')

   5:and s.last_call_et >= 2 * 60*60

   6:and s.status = ' INACTIVE '

   7:order by inst_id DESC

Next, create the stored procedure sys.db_kill_idle_clients. It is convenient to invoke this function to perform a kill inactive session. Note:the xxx section is replaced with the actual business program.

   2:create OR REPLACE PROCEDURE SYS. Db_kill_idle_clients AUTHID Definer as

   3:    job_no number;

   4:    num_of_kills Number: = 0;

   5:begin

   7: For    REC in

   8:        (SELECT SID, serial#, inst_id, Module,status

   9: From         gv$session S

  Ten:             WHERE s.username is not NULL

  One:             and UPPER (S.program) in (' xxx ', ' xxx. EXE ')   

  : and                  S.last_call_et >= 2*60*60                              

  : and             s.status= ' INACTIVE '

  :         ORDER by inst_id ASC

  :             ) LOOP

  :          ---------------------------------------------------------------------------

  :          --Kill inactive sessions immediately

  :          ---------------------------------------------------------------------------

  :          dbms_output. PUT (' LOCAL SID ' | | rec.sid | | ' (' | | | rec.module | | ‘)‘);

  :      execute immediate ' alter system kill session ' | | rec.sid | | ', ' | |

  £                             rec.serial# | | "' Immediate ';

  22:      

  At:          Dbms_output. Put_Line ('. Killed locally ' | | job_no);

  :          num_of_kills: = num_of_kills + 1;

  :    END LOOP;

  A:    dbms_output. Put_Line (' Number of killed XXXX system sessions: ' | | num_of_kills);

  27:end db_kill_idle_clients;

  28:/

In addition, since kill session is to kill the session directly, there may be a phenomenon that causes things to rollback, in fact, we can use the disconnect session to complete the current transaction and terminate the session. This method is more secure than the ALTER system kill session.

   1:create OR REPLACE PROCEDURE SYS. Db_kill_idle_clients AUTHID Definer as

   2:    job_no number;

   3:    num_of_kills Number: = 0;

   4:begin

   6: For    REC in

   7:        (SELECT SID, serial#, inst_id, Module,status

   8: from         gv$session S

   9:             WHERE s.username is not NULL

  Ten:             and UPPER (S.program) in (' xxxx ', ' xxxx ')   

  One: and                  s.last_call_et >= 2*60*60                              

  : and             s.status<> ' killed '

  :         ORDER by inst_id ASC

  :             ) LOOP

  :          ---------------------------------------------------------------------------

  :          --Kill inactive sessions immediately

  :          ---------------------------------------------------------------------------

  :          dbms_output. PUT (' LOCAL SID ' | | rec.sid | | ' (' | | | rec.module | | ‘)‘);

  :          execute immediate ' alter system disconnect session ' | | rec.sid | | ', ' | |

  :                             rec.serial# | | "' Immediate ';

  A:          dbms_output. Put_Line ('. Killed locally ' | | job_no);

            Num_of_kills: = num_of_kills + 1;

  :    END LOOP;

  :    dbms_output. Put_Line (' Number of killed system sessions: ' | | num_of_kills);

  26:end db_kill_idle_clients;

  27:/

We can then periodically invoke the stored procedure either in the job or in the schedule, or through a background job combined with a shell script to periodically clean up the idle session. For example, the following is shown.

Create a killsession.sh script that calls the stored procedure sys.db_kill_idle_clients

   1: #!/bin/bash

   5:logfile=/home/oracle/cron/session/log/killsession.log

   7:echo "" >> $logfile 2>&1

   8:echo "START----' Date '" >> $logfile 2>&1

   9:sqlplus/nolog <<stats

  10:connect/as SYSDBA

  11:exec sys.db_kill_idle_clients;

  12:exit;

  13:stats

  15:echo "END------' Date '" >> $logfile 2>&1

Configure background jobs in crontab, run every 15 minutes, and clean up any idle sessions that meet the criteria.

0,15,30,45 * * * */home/oracle/cron/session/bin/killsession.sh >/dev/null 2>&1

Oracle regularly cleans up inactive sessions