This procedure will allow you to give a user the ability to kill any session other than SYS, SYSTEM, DBSNMP, REPADMIN,...
OUTLN and the background processes without giving them the alter system privilege.
Here are the administrative instructions to install and run the kill_session procedure:
1. Connect as sys. Compile/create the procedure.
2. Grant the user the ability to execute the procedure:
grant execute on sys.kill_session to <username>;
3. Create a synonym for the sys.kill_session:
create synonym <username>.kill_session for sys.kill_session;
4. Optional testing. Then you can execute the procedure.
5. Connect to the database with the user you set up the grant for:
set serveroutput on size 1000000 execute kill_session('<LIST|<SID value>>);
To see output from the execute command, you must issue the set serveroutput on, before executing the procedure. The kill_session procedure takes one parameter. It can either be:
- LIST - Lists the SID, DB user, O/S user, application that you can kill. List is case insensitive.
- <SID> The SID number that you get from the list. This user will be killed.
When LIST is the parameter you will receive output like this:
SID, DB User, O/S user, Application ===================================== 15, TEST, Administrator, sqlplus.exe
When <SID> is the parameter you will receive output like this:
User TEST with SID 15 was killed successfully. The O/S Process ID to kill is 2407.
Note: If the application that started the session is still running, you can either exit the application or go out to the UNIX side and kill it. We have provided you the PID.
We have implemented error handling to account for incorrect parameters. Either way, you must pass the procedure one and only one parameter or it will error out. If you enter in an invalid SID like ABC123 or abc, you will get this exception:
Invalid parameter. Syntax: execute kill_session('<LIST|<SID value>>');
If you enter in a SID that does not exist or a SID that belongs to SYS, SYSTEM, DBSNMP,REPADMIN, OUTLN, you will get this exception:
<SID ENTERED> is not a valid SID. Please try another one.
Here's the code:
create or replace procedure kill_session(p_1 in varchar2) is v_1 varchar2(64) := upper(p_1); v_sid varchar2(254) := null; v_sid2 v$session.sid%TYPE := 0; v_sn v$session.serial#%TYPE := 0; cid integer := 0; v_pid v$process.spid%TYPE := 0; v_sqlstr varchar2(254) := null; v_usr v$session.username%TYPE := null; rows_processed integer := 0; BEGIN if v_1 = 'LIST' then dbms_output.put_line('SID, DB User, O/S user, Application' || chr(13) || '====================================='); for v_sid in (select sid,username, osuser, program from V$SESSION where type != 'BACKGROUND' and username not in ('SYS', 'SYSTEM', 'DBSNMP', 'REPADMIN', 'OUTLN') and status != 'KILLED' and sid != (select sid from V$SESSION where audsid = (select userenv('sessionid') from dual))) loop dbms_output.put_line(v_sid.sid || ', ' || v_sid.username || ', ' || v_sid.osuser || ', ' || v_sid.program); end loop; elsif v_1 is null or v_1 = '' then raise VALUE_ERROR; else v_sid2 := to_number(v_1); select serial#,username into v_sn,v_usr from V$SESSION where type != 'BACKGROUND' and username not in ('SYS', 'SYSTEM', 'DBSNMP', 'REPADMIN', 'OUTLN') and status != 'KILLED' and sid != (select sid from V$SESSION where audsid = (select userenv('sessionid') from dual)) and sid = v_sid2; if SQL%FOUND then select spid into v_pid from v$session s, v$process p where s.username = v_usr and sid = v_sid2 and s.paddr = p.addr; v_sqlstr := 'ALTER SYSTEM KILL SESSION ''' || v_sid2 || ',' || v_sn || ''''; cid := Dbms_Sql.Open_Cursor; Dbms_Sql.parse(cid, v_sqlstr, Dbms_Sql.Native); rows_processed := dbms_sql.execute(cid); DBMS_SQL.CLOSE_CURSOR(cid); dbms_output.put_line('User ' || v_usr || ' with SID ' || v_sid2 || ' was killed successfully.' || chr(13) || 'The O/S Process ID to kill is ' || v_pid || '.'); end if; end if; exception when NO_DATA_FOUND then dbms_output.put_line(v_1 || ' is not a valid SID. Please try another one.'); when INVALID_NUMBER or VALUE_ERROR then dbms_output.put_line('Invalid parameter.' || chr(13) || 'Syntax: execute kill_session(''<LIST|<SID value>>'');'); when others then dbms_output.put_line('kill_session() encountered error ' || to_char(SQLCODE) || '. Please try again.'); END; /
Mona E. writes: I can't get this procedure to work. The list function does not list the session:
SQL> exec kill_session ('LIST 23'); Invalid parameter. Syntax: execute kill_session('>'); PL/SQL procedure successfully completed.
Gaurav J. writes: This procedure can be customised to give users the facility (and restrict them) to kill sessions started from their own terminal and for their own username, using the userenv fuction.
For More Information
- What do you think about this tip? E-mail the Editor at firstname.lastname@example.org with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.