Automate kill inactive user in oracle database

Step by step to automatically kill inactive user in oracle database
To see inactive user in oracle database you can use this query “select * from v$session where status=’INACTIVE’ or user=’sys’”
1. Create procedure for killing inactive oracle database user (oracle user have to be grant dbms_sql to execute this procedure)
CREATE OR REPLACE PROCEDURE kill_session_1 ( session_id in varchar2,serial_num in varchar2)
AS
cur INTEGER;
ret INTEGER;
string VARCHAR2(100);
BEGIN
–
– Comment out the following three lines to
– not use KILL
–
string :=’ALTER SYSTEM KILL SESSION’ || CHR(10) ||CHR(39)||session_id||’,'||serial_num||CHR(39);
–
– Uncomment the following 4 lines to use DISCONNECT
–
–string := ‘ALTER SYSTEM DISCONNECT SESSION’ || CHR(10) || CHR(39)||session_id||’,'||serial_num||CHR(39)||CHR(10)||’ POST_TRANSACTION’;
cur := dbms_sql.open_cursor;
dbms_sql.parse(cur,string,dbms_sql.native);
ret := dbms_sql.execute(cur) ;
dbms_sql.close_cursor(cur);
END;
/
2. Create sql script ORA_KILL.sql for spooling command to execute procedure created (see no 1)
REM
REM ORA_KILL.SQL
REM FUNCTION: Kills nonessential Oracle sessions (those that aren’t
REM owned)
REM : by SYS or “NULL”
REM DEPENDENCIES: Depends on kill_session procedure
REM MRA 9/12/96
REM
SET HEADING OFF TERMOUT OFF VERIFY OFF ECHO OFF
SPOOL kill_all.sql
SELECT ‘EXECUTE kill_session_1(‘||chr(39)||sid||chr(39)||’,'||
chr(39)||serial#||chr(39)||’);’ FROM v$session
WHERE username = ‘ECARE2′ and status=’INACTIVE’
/
SPOOL OFF
START kill_all.sql

3. Create shell script to automate execute sql script using cronjob (for unix user)
sqlplus -s $schema/$pass@$dbLink as sysdba<<!
set timing on
@ORA_KILL