ORACLE regularly clears INACTIVE sessions

Welcome to the Oracle community forum and interact with 2 million technical staff. In addition, because killsession directly kills sessionkill, transaction rollback may occur, in fact, we can use disconnectsession to complete the current transaction and terminate the session. this method is safer and more reliable than altersystemkillsession.

Welcome to the Oracle community forum, and interact with 2 million technical staff> In addition, the kill session directly kills the session, which may cause transaction rollback, in fact, we can use disconnect session to complete the current transaction and terminate the session. this method is safer and more reliable than alter system kill session.

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

In addition, the kill session directly kills the session and may cause transaction rollback. In fact, we can use the disconnect session to complete the current transaction and terminate the session. this method is safer and more reliable than alter system kill session.

1: create or replace procedure sys. DB_KILL_IDLE_CLIENTS AUTHID DEFINER

2: job_no number;

3: num_of_kills number: = 0;

4: BEGIN

5:

6: FOR REC IN

7: (select sid, SERIAL #, INST_ID, MODULE, STATUS

8: FROM gv $ session S

9: where s. USERNAME IS NOT NULL

10: and upper (S. PROGRAM) IN ('xxxx', 'xxxx ')

11: and s. LAST_CALL_ET> = 2*60*60

12: and s. STATUS <> 'killed'

13: order by INST_ID ASC

14:) LOOP

15 :---------------------------------------------------------------------------

16: -- kill inactive sessions immediately

17 :---------------------------------------------------------------------------

18: DBMS_OUTPUT.PUT ('local Sid' | rec. SID | '(' | rec. module | ')');

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

20: rec. serial # | '''immediate ';

21:

22: DBMS_OUTPUT.PUT_LINE ('. killed locally' | job_no );

23: num_of_kills: = num_of_kills + 1;

24: end loop;

25: DBMS_OUTPUT.PUT_LINE ('number of killed system sessions: '| num_of_kills );

26: END DB_KILL_IDLE_CLIENTS;

27 :/

Then, we can regularly call the stored procedure in a JOB or Schedule, or use a background JOB and shell script to regularly clean up idle sessions. For example.

Create a killSession. sh script and call the stored procedure SYS. DB_KILL_IDLE_CLIENTS.

1 :#! /Bin/bash

2:

3:

4:

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

6:

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

14:

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

Configure the background job in the crontab and run it every 15 minutes to clear idle sessions that meet the conditions.

, ****/Home/oracle/cron/session/bin/killSession. sh>/dev/null 2> & 1

[1] [2]