Posted by As developers, sometimes we set something running that we wish we hadn’t 
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
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'));
Learning is not a spectator sport 
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.
Valid point.