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'));

Advertisements

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