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
