Session killin’ time

As developers, sometimes we set something running that we wish we hadn’t Smile  And naturally, we’d like to be good IT citizens and clean up the mess as quick as we can.  (For most of us, this means – cover our tracks before the phone rings about smoke coming out of the server).  But of course, getting an administrator to hand over the trigger to let you have the ALTER SYSTEM KILL SESSION command is probably unlikely because…well… it’s just a bad bad idea.  So here’s a wrapper which might serve as a starting point for you.  It expose the kill system command to you, but in a restricted set of circumstances.

Sample Usage

By default, we report any session that has a status of active or killed. We’ll see the session details, whether it’s running or blocked, plus the SQL ID etc.


SQL> select * from table(sys.my_session.s);

Session User/Elapsed Secs Current SQL Status Program Blocked by Session
--------------------------------------------------------------------------------------------------------------------------------
257,5501 ASKTOM (0) 89uk42w1xkdty ACTIVE sqlplus.exe-comcdona
261,8848 QDB_PROD (33747) KILLED SQL Developer-stevenfeuerstein
368,43752 ASKTOM (12) a40p1nyb24j18 ACTIVE sqlplus.exe-comcdona

Alternatively, we can pass in “ALL” to see all sessions



SQL> select * from table(sys.my_session.s('all'));

Session User/Elapsed Secs Current SQL Status Program Blocked by Session
--------------------------------------------------------------------------------------------------------------------------------
1,13808 SYS (1769614) INACTIVE VKTM-oracle
2,23469 SYS (1769611) INACTIVE DIAG-oracle
3,36185 SYS (1769611) INACTIVE DBW0-oracle
4,21472 SYS (1769611) INACTIVE SMON-oracle
5,8033 SYS (1769611) INACTIVE PXMN-oracle
6,16680 SYS (1769605) INACTIVE TMON-oracle
7,61493 SYS (1769605) INACTIVE ARC3-oracle
9,16830 APEX_PUBLIC_USER (1122) INACTIVE APEX Listener-oracle
11,3902 SYS (1769590) INACTIVE CJQ0-oracle
12,20631 SYS (1769587) INACTIVE QM00-oracle
14,50003 APEX_LISTENER (949) INACTIVE APEX Listener-oracle
17,2037 SYS (1769585) INACTIVE Q009-oracle
22,41550 SYS (125) INACTIVE W002-oracle
26,52963 ORDS_PUBLIC_USER (99637) INACTIVE APEX Listener-oracle
28,27784 SYS (3693) INACTIVE W007-oracle
30,9396 ORDS_PUBLIC_USER (1306) INACTIVE APEX Listener-oracle
119,21406 SYS (1769614) INACTIVE GEN0-oracle
120,9696 SYS (1769611) INACTIVE DBRM-oracle
121,65040 SYS (1769611) INACTIVE LGWR-oracle
122,64828 SYS (1769611) INACTIVE LG01-oracle
123,65400 SYS (1769611) INACTIVE MMON-oracle
...
...

By default, you can just call the “kill” routine and we’ll look for a single session that

  • has been active
  • for more than 10 seconds,
  • is owned by you,
  • is within a list of known user accounts is running on your terminal,
  • came from sqlplus or sql developer,
  • is not a parallel slave

etc etc….Basically you edit the code (at the end of this blog) to be as restrictive as required to protect people from themselves Smile

I’m just dbms_output-ing in this case, but you get the idea. We first try a kill, and then attempt a disconnect as well.



SQL> exec sys.my_session.kill
alter system kill session '368,43752' immediate
alter system disconnect session '368,43752'

PL/SQL procedure successfully completed.

You can see that this was the only applicable ASKTOM process from the active list in the first query.

If we don’t find a session, or find more than 1 session, you’ll get errors, eg



SQL> exec sys.my_session.kill
BEGIN my_session.kill; END;

*
ERROR at line 1:
ORA-20000: No suitable session found for killing

SQL> exec sys.my_session.kill
BEGIN my_session.kill; END;

*
ERROR at line 1:
ORA-20000: More than one session found. Pass in the SID number

In the latter case, you can do a more selective kill by passing in the SID, eg



SQL> exec sys.my_session.kill(368)
alter system kill session '368,43752' immediate

PL/SQL procedure successfully completed.

Enjoy !

Source Code


create or replace
package sys.my_session is
  procedure kill(p_sid number default null);
  function s(p_type varchar2 default 'ACTIVE') return sys.odcivarchar2list pipelined;
end;
/
--
-- Choose (wisely) which schemas you want to give access to this
--
grant execute on sys.my_session to ???????;
create or replace
package body sys.my_session is
  procedure kill(p_sid number default null) is
    l_sid     int;
    l_serial# int;
    l_user    varchar2(30) := user;
  begin
    select s.sid, s.serial#
    into   l_sid, l_serial#
    from   v$session s,
           v$px_session p
  --
    -- Here is where you implement all the rules for controlling exactly *what* sessions
    -- people will be allowed to kill.
    --
  --
    -- must be my own login
    --
    where  s.username = l_user
    --
    -- must be running something for 10 seconds or more
    --
    and    s.status = 'ACTIVE'
    and    s.last_call_et > 10
    --
    -- must be SQL Dev or SQL Plus
    --
    and    ( upper(s.program) like '%SQLPLUS%'
          or upper(s.program) like '%SQL%DEVELOPER%'
           )
    --
    -- must be an account we're allowed to kill (just in case they circumvent the 'own login' check above)
    --
    and   s.username in ('ASKTOM','QDB_PROD')
    --
    -- must be on the same machine as the problem session
    --
    and   upper(s.terminal) = upper(sys_context('USERENV','TERMINAL'))
    --
    -- allow override for explicit sid
    --
    and   s.sid = nvl(p_sid,s.sid)
    --
    -- must not be a parallel slave
    --
    and   s.sid = p.sid(+)
    and   s.serial# = p.serial#(+)
    and   s.sid != p.qcsid(+)
    and   p.sid is null;
  begin
      dbms_output.put_line('alter system kill session '''||l_sid||','||l_serial#||''' immediate');
  --    execute immediate 'alter system kill session '''||l_sid||','||l_serial#||''' immediate';
    exception
      when others then null;
    end;
  begin
      dbms_output.put_line('alter system disconnect session '''||l_sid||','||l_serial#||'''');
  --    execute immediate 'alter system disconnect session '''||l_sid||','||l_serial#||'''';
    exception
      when others then null;
    end;

  exception
    --
    -- No session found, means you're fine or trying to be nasty
    --
    when no_data_found then
       raise_application_error(-20000,'No suitable session found for killing');
    --
    -- More than one session found, means we'll need the sid explicitly
    --
    when too_many_rows then
       raise_application_error(-20000,'More than one session found.  Pass in the SID number');
  end;
  function s(p_type varchar2 default 'ACTIVE') return sys.odcivarchar2list pipelined is
  begin
   for i in ( 
      select row_number() over ( order by s.sid)  r, s.sid, s.serial#,
                nvl(s.username,'SYS')
        || ' ('||s.last_call_et||')' username,
        case when s.lockwait is null then
             case when s.username is null then
                nvl2(j.job_sid,'ACTIVE','INACTIVE')
             else
                s.status
             end
        else 'BLOCKED'
        end status,
        nvl(s.sql_id,' ') sql_id,
        nvl(case when s.program is not null then
               ( case when s.program like 'oracle%(%)%' then regexp_substr(s.program,'^oracle.*\((.*)\).*$',1, 1, 'i', 1)
                      else s.program
                      end )
             when s.username is null then ( select p.program
                                            from   v$process p
                                            where  s.PADDR = p.ADDR )
             end || '-' || s.osuser,' ') program ,
             nvl(to_char(blocking_session),' ') blocking_session,
             nvl(case
               when blocking_session is null then cast(null as varchar2(1))
               else
                cast(( select substr(s1.osuser||'-'||s1.program,1,60)
                  from   v$session s1
                  where s1.sid = s.blocking_session
                ) as varchar2(60))
             end,' ') blocker
      from v$session s,
                ( select sid job_sid
                  from   v$lock
                  where  type = 'JQ' ) j
      where s.sid = j.job_sid(+)
      and (
            ( upper(p_type) = 'ACTIVE' 
              and s.status in ('ACTIVE','KILLED')
              and ( s.username is not null or ( s.username is null and s.last_call_et < 300 ) )
            ) 
            or
            upper(p_type) = 'ALL'
          )
      order by s.sid
   ) 
   loop
     if i.r = 1 then
       pipe row ( rpad('Session',14)||
                  rpad('User/Elapsed Secs',24)||
                  rpad('Current SQL',16)||
                  rpad('Status',14)||
                  rpad('Program',40)||
                  rpad('Blocked by Session',20)
                 );
       pipe row ( rpad('-',14,'-')||
                  rpad('-',24,'-')||
                  rpad('-',16,'-')||
                  rpad('-',14,'-')||
                  rpad('-',40,'-')||
                  rpad('-',20,'-')
                 );
     end if;
     pipe row ( rpad(i.sid||','||i.serial#,14)||
                rpad(i.username,24)||
                rpad(i.sql_id,16)||
                rpad(i.status,14)||
                rpad(substr(i.program,1,38),40)||
                lpad(i.blocker,20)
              );
   end loop;
  end;
end;
/
sho err
select * from table(my_session.s('all'));

2 thoughts on “Session killin’ time

  1. Hi Connor,

    I would rather create such kind of stuff in a dedicated administrative schema.
    Definitely, not in a predefined database account.
    This adheres to the below recommendation from the Administrator’s guide:
    http://docs.oracle.com/database/122/ADMIN/getting-started-with-database-administration.htm#ADMIN11042
    Create at least one additional administrative user and grant to that user an appropriate administrative role to use when performing daily administrative tasks. Do not use SYS and SYSTEM for these purposes.

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