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.