Oracle cleans up inactive sessions in a database for long periods of time

1. Start the resource plan

Alter system set RESOURCE_LIMIT=TRUE Scope=spfile;

2. Set inactivity 15 minutes disconnect, release resources

Alter profile default limit Idle_time 15;

3. Clear the Inactive process (No 10 minutes to send the test pack)

Add Sqlnet $ORACLE _home/network/admin. expire_time=10;

SELECT SID, Serial#,module, STATUS

From V$session S

WHERE S.username Isnotnull

Andupper (S.program) in (' TOAD. EXE ', ' w3wp. EXE ')

and S.last_call_et >= 60*60*2

and s.status = ' INACTIVE '

SID DESC;

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

SELECT SID, serial#, inst_id, Module,status

From Gv$session S

WHERE S.username Isnotnull

Andupper (S.program) in (' TOAD. EXE ', ' w3wp. EXE ')

and S.last_call_et >= 2 * 60*60

and s.status = ' INACTIVE '

inst_id DESC

   create or replace procedure sys. db_kill_idle_clients authid definer as    job_no number;     num_of_kills number := 0; begin     for rec  IN         (select sid, serial#, inst_id,  module,status         from gv$session s              WHERE S.USERNAME IS NOT  Null             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 '  ;           dbms_output. Put_Line (' . killed locally  '  | |  JOB_NO);           num_of_kills := num_of_ Kills + 1;    end loop;    dbms_output. put_line  (' number of killed xxxx system sessions:  '  | |  num_of_kills);  end db_kill_idle_clients; /

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 cleans up inactive sessions in a database for long periods of time