Kill The Inactive Session After 12 Hours

Hi all,

If you can not set up an ad hoc profile to automatically end all sessions in idle state, perhaps this script could help you.

Oracle Single Instance:

select 'alter system kill session '''||sid||','||serial#||''';' from v$session WHERE status='INACTIVE' AND last_call_et > (60*60*12);

Example output:

alter system kill session ‘2545,48723’;
alter system kill session ‘5642,317’;

 

For automatic execution:
BEGIN
FOR R IN (select sid,serial# from v$session WHERE status='INACTIVE' AND last_call_et > (60*60*12);) LOOP
EXECUTE IMMEDIATE 'alter session kill '||R.SID||,||R.SERIAL#||';
END LOOP;
END;
/

For Oracle Rac environment:

select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''immediate;';' from gv$session WHERE STATUS='INACTIVE' AND last_call_et > (60*60*12);

Example output:

alter system kill session ‘2545,48723,@1’;
alter system kill session ‘5642,317,@2’;

For automatic execution:


BEGIN
FOR R IN (select SID,SERIAL#,INST_ID from GV$SESSION WHERE STATUS='INACTIVE' AND last_call_et > (60*60*12)) LOOP
EXECUTE IMMEDIATE 'alter system kill session ''' ||R.SID|| ',' ||R.SERIAL#|| ',@' ||R.INST_ID|| '''';
END LOOP;
END;
/

I hope this script helped you.

Leave a Reply

Your email address will not be published.