Killing oracle sessions, the easy (JDBC) way.

Does Oracle complain about not being able to drop a table for a currently connected user, but you are sure you disconnected? Do the sessions “hang” in “inactive” state? Just log on as system, and execute the following query: 

SELECT
'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || '''; --',
       s.sid,
       s.serial#,
       s.osuser,
       s.username,
       s.program,
	status
FROM   v$session s
WHERE status = 'INACTIVE';

You will get a list of statements you need to execute (just copy-pase) to kill the inactive sessions. Don’t listen to the guys telling you to do intricate System Administrator stuff on a command prompt, just use any JDBC tool.

EDIT: Single query which also lists the kill command if the “alter system kill session” trick did not work:

SELECT
'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# ||
       '''; -- kill -9 ' || p.spid,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program,
       s.status
FROM   v$session s, v$process p
WHERE s.paddr = p.addr
  and (s.state='INACTIVE' or s.state='KILLED');

Thanks Bas en Jeroen!

2 Responses to Killing oracle sessions, the easy (JDBC) way.

  1. rolfje says:

    Original comment by Jeroen:

    Leuk in combinatie hiermee: client identifier

    CREATE OR REPLACE TRIGGER LOGON_TRIGGER
    AFTER LOGON ON DATABASE
    DECLARE
    v_user_identifier varchar2(64);
    BEGIN
    SELECT SYS_CONTEXT(‘USERENV’, ‘OS_USER’)
    ||’:’||
    SYS_CONTEXT(‘USERENV’, ‘IP_ADDRESS’)
    INTO v_user_identifier
    FROM dual;
    DBMS_SESSION.SET_IDENTIFIER(v_user_identifier);
    END;

    daarna s.client_identifier toevoegen en hopla

  2. rolfje says:

    Original comment by Bas:

    The above won’t work if the session has status=killed. In that case retrieve the system process id with:

    select s.username,s.osuser,s.sid,s.serial#,p.spid
    from v$session s,v$process p
    where s.paddr = p.addr
    and s.username is not null;

    and use kill -9 on unix to kill the session.

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