Kill inactive sessions in scheduled time

We needed kill for all inactive reporting users which has been inactive log an hour. This must have been a scheduled time in future. We can use such a below method for this purpose.

- Create schedular job that contaning running time.
- Put the below PL/SQL block in action side in that job.


As a result, sessions are automatically closed. Of course, this only applied to risk-free for users who selects. Users in a transaction with the closure of such a method is risky. You can desire select statement which using v$session view.


begin
FOR i IN (select username from dba_users where username like 'TEMP%')
LOOP
execute immediate 'alter user ' || i.username || ' account lock';
END LOOP;
FOR i IN (select sid, serial#, username from v$session where username like 'TEMP%')
LOOP
EXECUTE IMMEDIATE 'alter system kill session ' || '''' || i.sid || ',' || i.serial# || ''' immediate ';
END LOOP;
end;
/