Here's an example of one my own that I've used on client sites in the past. You would (should) augment/test/tailor the script for your own needs
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
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 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.
</code>
SQL> exec sys.my_session.kill
alter system kill session '368,43752' immediate
alter system disconnect session '368,43752'
PL/SQL procedure successfully completed.
</code>
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, eg
SQL> exec sys.my_session.kill(368)
alter system kill session '368,43752' immediate
PL/SQL procedure successfully completed.
And here is the source
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 ('??????','??????')
--
-- 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'));