Schedule Killing sessions dynamically

You can kill session dynamically as below in a single instance but for the RAC you may face issues , so approach below can be used.

declare
stmt varchar2(300);
cursor c1 is select * from v$session
where LAST_CALL_ET > 1800 AND PROGRAM='w3wp.exe';
REC v$session%rowtype;
counter number := 0;
begin
    OPEN c1;   
LOOP
    FETCH c1 into REC;
    EXIT when c1%notfound;
        stmt := 'alter system kill session ''' ||REC.sid||','||REC.serial#||''''||' IMMEDIATE';
        --stmt := 'alter system kill session '''||p_sid||','||p_serial#||',@'||inst_id||''''||' IMMEDIATE';
        --dbms_output.put_line(stmt);
        execute immediate stmt;
        counter := counter +1;
END LOOP;
    CLOSE c1;
    dbms_output.put_line(counter||' sessions Killed');
   
Exception
WHEN others THEN
        dbms_output.put_line('Exception: '||SQLERRM);
end;
/

If you want to kill as scheduled then use the windows schedule manager and make the batch file to call this code. As an example following may be used.
1- change in sqlplus' glogin file

spool d:\temp\killsessLastInfo
@d:\temp\killsess.sql
spool off
exit

2- Make killsess.sql containing the code above which you used in glogin.

Make a batch file (eg; killsess.bat) to be used by windows scheduler
sqlplus sys/oracle10g@instance as sysdba

when windows scheduler will run the batch file it will call the sqlplus which will run the killsess.sql as mentioned in sqlplus' glogin.

All files examples are below (ran on RAC 10g).

killsess.sql

spool d:\temp\killsessLastInfo

select 'Killing Sessions for w3wp.exe on '||instance_name||' On Server '||host_name||' @ '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')  " "
from v$instance
/

declare
stmt varchar2(300);
p_sid number:= 1631;
p_serial# number :=32849;
inst_id number := 2;
cursor c1 is select * from v$session
where LAST_CALL_ET > 1800 --AND PROGRAM='w3wp.exe'
and program not like '%ORACLE.EXE%'
order by program,last_call_et desc
;
REC v$session%rowtype;
counter number := 0;
begin
    DBMS_OUTPUT.enable(1000000);
    OPEN c1;  
LOOP
    FETCH c1 into REC;
    EXIT when c1%notfound;
        stmt := 'alter system kill session ''' ||REC.sid||','||REC.serial#||''''||' IMMEDIATE';
        --stmt := 'alter system kill session '''||p_sid||','||p_serial#||',@'||inst_id||''''||' IMMEDIATE';
        dbms_output.put_line(stmt||'  ('||REC.program||' - '||REC.terminal||' - '||REC.username||'- '||REC.LAST_CALL_ET||')');
        --execute immediate stmt;
        counter := counter +1;
END LOOP;
    CLOSE c1;
    dbms_output.put_line(counter||' sessions Killed');
    --DBMS_OUTPUT.disable;
Exception
WHEN others THEN
        dbms_output.put_line('Exception: '||SQLERRM);
end;

select 'Killed Sessions for w3wp.exe on '||instance_name||' On Server '||host_name||' @ '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') " "
from v$instance
/

spool off
exit


killInst1.bat
set day=%date:~0,3%
set mm=%date:~4,2%
set dd=%date:~7,2%
set yy=%date:~-4%
set hh=%time:~0,2%
set mi=%time:~3,2%
set ss=%time:~6,2%
sqlplus "sys/oracle@inst1 as sysdba" @killsess.sql
rename killsessLastInfo.LST killsessLastInfo_%yy%%mm%%day%%hh%%mi%%ss%.txt
 killInst2.bat
set day=%date:~0,3%
set mm=%date:~4,2%
set dd=%date:~7,2%
set yy=%date:~-4%
set hh=%time:~0,2%
set mi=%time:~3,2%
set ss=%time:~6,2%
sqlplus "sys/oracle@inst2 as sysdba" @killsess.sql
rename killsessLastInfo.LST killsessLastInfo_%yy%%mm%%day%%hh%%mi%%ss%.txt