About Killing Session at Oracle

Get The session ID by this query:

 

select * from v$session
where program like 'sqlplus@s61cj185%'</pre>
set feedback off
 set serveroutput on size 9999
 column username format a20
 column sql_text format a55 word_wrapped
 begin
 for x in
 (select username||'('||sid||','||serial#||') ospid = '|| process ||
 ' program = ' || program username,
 to_char(LOGON_TIME,' Day HH24:MI') logon_time,
 to_char(sysdate,' Day HH24:MI') current_time,
 sql_address,
 sql_hash_value
 from v$session
 where status = 'ACTIVE'
 and rawtohex(sql_address) <> '00'
 and username is not null ) loop
 for y in (select sql_text
 from v$sqlarea
 where address = x.sql_address ) loop
 if ( y.sql_text not like '%listener.get_cmd%' and
 y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%' ) then
 dbms_output.put_line( '--------------------' );
 dbms_output.put_line( x.username );
 dbms_output.put_line( x.logon_time || ' ' || x.current_time || ' SQL#=' || x.sql_hash_value);
 dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
 end if;
 end loop;
 end loop;
 end;
 /

And kill using this query:

ALTER SYSTEM DISCONNECT SESSION ‘7,36875’ IMMEDIATE;

or
ALTER SYSTEM KILL SESSION ‘sid,serial#’ IMMEDIATE

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: