Oracle kill_session procedure code script?

San
Posts: 24
Joined: Sun Oct 09, 2016 2:53 pm

Oracle kill_session procedure code script?

Postby San » Sun Oct 09, 2016 3:02 pm

Oracle kill_session procedure code script?

sprasad
Posts: 24
Joined: Sun Oct 09, 2016 2:52 pm

Re: Oracle kill_session procedure code script?

Postby sprasad » Sun Oct 09, 2016 3:03 pm

kill_session procedure code.


CREATE OR REPLACE PROCEDURE kill_session ( session_id in
varchar2,
serial_num in varchar2)
AS
cur INTEGER;
ret INTEGER;
string VARCHAR2(100);
BEGIN
string :=
'ALTER SYSTEM KILL SESSION' || CHR(10) ||
CHR(39)||session_id||','||serial_num||CHR(39);
-- 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.v7);
ret := dbms_sql.execute(cur) ;
dbms_sql.close_cursor(cur);
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'Error in execution',TRUE);
IF dbms_sql.is_open(cur) THEN
dbms_sql.close_cursor(cur);
END IF;
END;
/

ORA_KILL.SQL procedure for killing
nonessential Oracle sessions.
REM
REM ORA_KILL.SQL
REM FUNCTION: Kills non-essential Oracle sessions (those that
aren't owned
REM : by SYS or "NULL"
REM DEPENDANCIES: 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('||chr(39)||sid||chr(39)||','||
chr(39)||serial#||chr(39)||');' FROM v$session
WHERE username IS NOT NULL
OR username <> 'SYS'
/
SPOOL OFF
START kill_all.sql



LISTING 1 Example kill.sql script (output from
ora_kill.sql).

EXECUTE kill_session('10','212');
EXECUTE kill_session('13','1424');

Shell script to kill nonessential Oracle
processes from the server side.

#!/bin/ksh
ORATAB=/etc/oratab
trap 'exit' 1 2 3
# Set path if path not set (if called from /etc/rc)
case $PATH in
"") PATH=/bin:/usr/bin:/etc
export PATH ;;
esac
rm kill.lis
rm proc.lis
touch kill.lis
touch proc.lis
#
# Loop for every entry in oratab
#
cat $ORATAB | while read LINE
do
case $LINE in
\#*) ;; #comment-line in oratab
*)
ORACLE_SID='echo $LINE | awk -F: '{print $1}' -'
if [ "$ORACLE_SID" = '*' ] ; then
ORACLE_SID=""
fi
esac
if [ "$ORACLE_SID" <> '*' ] ; then
proc_name='oracle'$ORACLE_SID
ps -ef|grep $proc_name>>proc.lis
fi
done
cat proc.lis | while read LINE2
do
command='echo $LINE2 | awk -F: 'BEGIN { FS = ",[ \t]*|[
\t]+" }
{ print $2}' -'
test_it='echo $LINE2|awk -F: 'BEGIN { FS = ",[ \t]*|[
\t]+" }
{ print $8}' -'
if [ "$test_it" <> 'grep' ] ; then
command='kill -9 '$command
echo $command>>kill.lis
fi
done
rm proc.lis
chmod 755 kill.lis
kill.lis
rm kill.lis

LISTING 2 Example output from the ora_kill.sh script
(kill.lis).
kill -9 11240
kill -9 11244
kill -9 11248
kill -9 11252
kill -9 11256
kill -9 9023
kill -9 9025
kill -9 9028
kill -9 9030

Summary
It may be required to terminate nonessential Oracle sessions if these sessions are
“abandoned” by their users, or a shutdown must be accomplished regardless of database activity.
This termination can be accomplished with one of three methods: a shutdown with the abort
option, use of the ALTER SYSTEM kill option, or use of the operating system process killer. In
this paper we have discussed these methods, given examples and shown scripts on how to do each
process.


Return to “Scripting”

Who is online

Users browsing this forum: No registered users and 0 guests