Posted by Working with application developers, I am often asked to kill orphan sessions stuck in Oracle. These sessions sometime use a lot of resources or lock objects and rows. Developers usually work with unstable applications that crash a lot, or debuggers allowing the developer to stop the application process abruptly so they skip the commit/rollback part and even the disconnect part of the code. The client program is killed and does not report to Oracle server to cancel the statement and disconnect in a proper manner. This might produce an orphan process in the database. True, Oracle (PMON background process) will kill these orphan processes by itself, but it can take a while before they are identified and killed, and the developers could be facing locks or the database could be heavily loaded. When the development environment is relatively small, it is easy to identify the sessions that need to be killed. But in a large environment with numerous developers connected to the same database server with sometimes the same username, it is a little bit tricky. Moreover, a RAC environment makes this job of “killing sessions” more tedious. Addressing this time consuming repetitive task, I have created a very easy to use mechanism that will allow a developer to kill his/her own sessions without my help. My assumptions are:
- Developers are not granted the alter system / alter user privileges.
- When creating users for developers the DBA can add a prefix to the name (DEV_USER1, DEV_JOHN, DEV_RACHEL …).
- Developers do not share the same username.
- Multiple developers can run from the same client using different usernames to login to Oracle.
- A developer can run from multiple client machines – in this case he/she will want to kill sessions opened only from one specific client machine
Tested on:
- Oracle 10g
- Oracle 10g RAC
- Oracle 11g
- Oracle 11g RAC
Now, I will display the setup code, followed by an explanation: Create owner for the procedure (schema)
create user killer identified by killer; grant alter system to killer; grant select on v_$session, gv_$session to killer; alter user killer account lock;
Create the procedure
create or replace PROCEDURE killer.KILL_MY_CLIENTS AUTHID DEFINER AS job_no number; user_prefix varchar(20); domain_name varchar(20); num_of_kills number := 0; BACKSLASH constant char(1) := chr(92); begin user_prefix := 'DEV'; -- All my developer username prefixed with DEV -- (DEV_USER1, DEV_USER2, DEV_MIKE, DEV_JOHN ...) domain_name := 'WORKGROUP'; -- We work in a workgroup. -- Once we work in a windows domain this variable should -- hold the domain name for REC in (SELECT SID, SERIAL#, INST_ID, MODULE, SYS_CONTEXT('USERENV', 'INSTANCE') MY_INST_ID from gv$session s where s.username like user_prefix || '%' -- precaution and s.username = sys_context('USERENV', 'SESSION_USER') -- only my username and not (S.SID = SYS_CONTEXT('USERENV', 'SID') -- excluding my session and INST_ID = SYS_CONTEXT('USERENV', 'INSTANCE')) and replace(upper(machine), domain_name || BACKSLASH, '') -- my machine = replace(upper(sys_context('USERENV', 'HOST')), domain_name || BACKSLASH, '') and s.module not in ('SQL*Plus', 'SQL Developer') -- not sqlplus -- nor sql developer and s.module not like 'sqlplus@%' and S.LOGON_TIME < sysdate-(1/24/60/60) -- logged-on more -- than a second ORDER BY inst_id asc ) LOOP if rec.inst_id = rec.my_inst_id then --------------------------------------------------------------------------- -- kill local sessions immediately --------------------------------------------------------------------------- dbms_output.put('LOCAL SID ' || rec.sid || '(' || rec.module || ')'); execute immediate 'alter system kill session ''' || rec.sid || ', ' || rec.serial# || '''' ; dbms_output.put_line('. killed locally ' || job_no); ELSE --------------------------------------------------------------------------- -- kill remote sessions (for RAC) as a job --------------------------------------------------------------------------- DBMS_OUTPUT.PUT('REMOTE SID ' || REC.SID || ' on instance ' || REC.INST_ID || '(' || rec.module || ')'); DBMS_JOB.SUBMIT ( job => job_no, what => 'execute immediate ''alter system kill session ''''' || rec.sid ||', ' || rec.serial# || ''''''';', INSTANCE => rec.inst_id); dbms_output.put_line('. killed with job ' || job_no); COMMIT; end if; num_of_kills := num_of_kills + 1; end loop; dbms_output.put_line ('number of killed sessions: ' || num_of_kills); end kill_my_clients;
Make procedure available to all
create public synonym kill_my_clients for killer.kill_my_clients; grant execute on killer.kill_my_clients to public; alter user killer account lock;
Use it: login as the developer from client machine to any application that can run statements like SQL*Plus
exec kill_my_clients
Explanation: The commands in the first section create a user called “killer”. This user is the owner of the procedure described in the next section. Notice that this user is very powerful because it can kill any user with the permission “alter system”. That is why we must limit access to it so it is locked. The grants on v_$session and gv_$session must be done by sys or other user with admin options on these views. The second section is the procedure itself. It should be compiled under “killer” (as the code demonstrates). Here is an explanation of this procedure:
- Identify all sessions running from the developer’s machine from his/hers username except for the session running this query in the current node and remote RAC nodes.
- If the session is on the same instance of the current connection – kill it immediately.
- If the session is on a different RAC node – kill it by submitting a job on that node.
There are 2 variables that I usually change when I deploy this procedure in an environment:
- USER_PREFIX – I try naming all the developer users with a prefix such as DEV_USER1, DEV_USER2, DEV_MIKE, DEV_JOHN … I use this prefix as a measure of precaution – I don’t want my procedure to kill any users not beginning with this prefix. This way if my developer is running this procedure from user sys or system by mistake it will do no harm.
- DOMAIN_NAME – when working with windows clients sometimes the application is prefixed with the domain name (such as SQL Developer) and sometimes not (such as SQL*Plus). This variable will become crucial to identifying sessions opened from the developer’s machine.
- Execute kill command on the local instance.
- Execute kill command on all remote instances of RAC by submitting jobs with instance affinity.
Another factor I take into account is that I don’t want to kill connections from SQL*Plus or from SQL Developer. If you are working with other programs such as PL/SQL Developer or Toad you might want to include them in the where clause as well.
The third section demonstrates how to use this mechanism: You just have to login as the developer from the client machine to any application that can run statements like SQL*Plus and execute the procedure (you do not have to use the owner’s name in the call because you have declared a public synonym for it).
Enjoy
