Wednesday, May 19, 2010

How to kill Oracle connections from UNIX and SQL command prompt?

How to kill all sessions from a specific user?

1) export ORACLE_SID=Prime

2) sqlplus / as sysdba

3) set serveroutput on;

4)

DECLARE
  CURSOR c1 IS
  select sid, serial# from v$session where username='SCOTT';
kill_it c1%ROWTYPE;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO kill_it;
    EXIT WHEN c1%NOTFOUND;
    BEGIN
      EXECUTE IMMEDIATE 'alter system kill session '''||
       kill_it.sid||', '||kill_it.serial#||'''';
    END;
  END LOOP;
  CLOSE c1;
END;
/

5) exit;


How to kill all sessions from any server except any connections coming from the database server?

1) export ORACLE_SID=Prime

2) sqlplus / as sysdba

3) set serveroutput on;

4)

DECLARE
  CURSOR c1 IS
  select sid, serial# from v$session where machine='production';
kill_it c1%ROWTYPE;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO kill_it;
    EXIT WHEN c1%NOTFOUND;
    BEGIN
      EXECUTE IMMEDIATE 'alter system kill session '''||
       kill_it.sid||', '||kill_it.serial#||'''';
    END;
  END LOOP;
  CLOSE c1;
END;
/

5) exit;


How to kill all sessions except any background processes and my current session;


1) export ORACLE_SID=Prime

2) sqlplus / as sysdba

3) set serveroutput on;

4)

DECLARE
  CURSOR c1 IS
  select
    sid, serial# from v$session
  where
    username is not null
    AND
    username not in
        (select
            username from v$session
        where
            sid =
                (select distinct sid from v$mystat));
kill_it c1%ROWTYPE;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO kill_it;
    EXIT WHEN c1%NOTFOUND;
    BEGIN
      EXECUTE IMMEDIATE 'alter system kill session '''||
       kill_it.sid||', '||kill_it.serial#||'''';
    END;
  END LOOP;
  CLOSE c1;
END;
/
5) exit;




Generate a SQL output that you can copy paste in the SQL Editor to kill all the Oracle Sessions (except the background processes)

set pagesize 100
select
    'Alter System Kill Session '''|| Sid || ',' || Serial# || ''' IMMEDIATE;'  as Kill_the_following_connections
from
    (select
        Sid,
        Serial#
    from
        v$session
    where
        username is not null
    and
        username not in ('SYS','SYSTEM','SYSMAN','DBSNMP'));


To kill the sessions from LINUX (except the background processes and local database connections)

ps -ef |grep oracle$ORACLE_SID |grep -v grep |grep -v PROTOCOL=beq |awk '{ print $2 }' |xargs kill -9

The above has worked for me. Although sometimes I see few stale connections that I can kill later with any of the above methods but mostly, the above works.

Below is my testing results. When time permits, I will add more on my steps but for now, lets just say it is pretty much self explanatory.

Let's see how many users are connected to the database.

set linesize 200
set username format a20
col machine format a20
set pagesize 60

select
    a.username, 
    a.machine,
    a.sid,
    a.serial#,
    b.spid,
    a.process
from    
    v$session a, v$process b
where
    a.paddr=b.addr
and a.username is not null
-- and a.username not in ('SYS','SYSTEM','DBSNMP','SYSMAN')
-- and a.machine like 'DR%'
-- and a.username='SCOTT'
-- and a.machine <> 'production'
order by
    spid;

USERNAME                       MACHINE                     SID    SERIAL# SPID         PROCESS
------------------------------ -------------------- ---------- ---------- ------------ ------------
DBSNMP                         production                  175         42 25213        13467
SYSMAN                         production                  156       1796 25237        1234
SYSMAN                         production                  164         41 25247        1234
DBSNMP                         production                  165        135 25281        13467
SYS                            production                  141      25733 25375        25374
SCOTT                          production                  182      45691 25437        25436
SCOTT                          AAA\1-11816-LAPXP           142      13808 25490        5352:5396
SYSTEM                         AAA\1-11816-LAPXP           163        245 25512        1724:5616

SYSMAN                         production                  162      37192 26511        1234
SYSMAN                         production                  154      64670 26516        1234
SYSMAN                         production                  181      34355 26520        1234
SYSTEM                         production                  170      33536 26551        1234
SYS                            production                  158      24367 26682        26681

13 rows selected.

SQL> !ps -ef |grep oracle$ORACLE_SID |grep -v grep |grep -v PROTOCOL=beq
oracle   25213     1  0 01:03 ?        00:00:00 oraclePrime (LOCAL=NO)
oracle   25237     1  0 01:03 ?        00:00:00 oraclePrime (LOCAL=NO)
oracle   25247     1  0 01:03 ?        00:00:00 oraclePrime (LOCAL=NO)
oracle   25281     1  0 01:04 ?        00:00:01 oraclePrime (LOCAL=NO)
oracle   25490     1  0 01:06 ?        00:00:00 oraclePrime (LOCAL=NO)
oracle   25512     1  0 01:06 ?        00:00:00 oraclePrime (LOCAL=NO)
oracle   26511     1  0 01:10 ?        00:00:01 oraclePrime (LOCAL=NO)
oracle   26516     1  0 01:11 ?        00:00:00 oraclePrime (LOCAL=NO)
oracle   26520     1  0 01:11 ?        00:00:00 oraclePrime (LOCAL=NO)
oracle   26551     1  0 01:11 ?        00:00:00 oraclePrime (LOCAL=NO)


As you can see some of the SPID are associated with DBSNMP and SYSMAN schemas. At this point, you can skip all those users by manually killing each session minus any DBSNMP/SYSMAN connections
OR
use the following method to kill all the connections which are not local to the DB Server. This includes all DBSNMP and SYSMAN connections as well.

SQL> !ps -ef |grep oracle$ORACLE_SID |grep -v grep |grep -v PROTOCOL=beq |awk '{ print $2 }'
25213
25237
25247
25281
25490
25512
26511
26516
26551

SQL> !ps -ef |grep oracle$ORACLE_SID |grep -v grep |grep -v PROTOCOL=beq |awk '{ print $2 }' |xargs kill -9

At this point, I check the outside (local=no) connections, and I see the following.

SQL> SQL> !ps -ef |grep oracle$ORACLE_SID |grep -v grep |grep -v PROTOCOL=beq
oracle   27400     1  0 01:21 ?        00:00:00 oraclePrime (LOCAL=NO)
oracle   27402     1  0 01:21 ?        00:00:00 oraclePrime (LOCAL=NO)
oracle   27438     1  0 01:22 ?        00:00:00 oraclePrime (LOCAL=NO)

If my assumption is right, DBSNMP and SYSMAN connections connected back after they realized that connection was lost. Lets check it out

select
    a.username, 
    a.machine,
    a.sid,
    a.serial#,
    b.spid,
    a.process
from    
    v$session a, v$process b
where
    a.paddr=b.addr
-- and
--    a.username not in ('SYS','SYSTEM','DBSNMP','SYSMAN')
-- and
--    a.machine like 'DR%'
-- and   
--    a.username='SCOTT'
and
    a.username is not null
--and
--    a.machine <> 'production'
order by
    spid;


USERNAME                       MACHINE                     SID    SERIAL# SPID         PROCESS
------------------------------ -------------------- ---------- ---------- ------------ ------------
SYS                            production                  158      24367 26682        26681
DBSNMP                         production                  182      45779 29655        13467
SYSMAN                         production                  165        145 29657        1234
SYSMAN                         production                  145      17657 29668        1234


As you can see, every outside connection is dead except the connections which are internal and connections from DBSNMP and SYSMAN. Althought more testing is needed, my guess is dbconsole will invoke its connections after they shutdown ungracefully. If you are still in doubt, then restarting dbconsole won't hurt.

The above method of killing Oracle connections from outside of SQL Plus has always worked for me. If you have any other idea or have a better way, please share with us.


Thanks,
--Moid Muhammad















Other Notes:

Note-1

Before killing sessions, if possible stop new sessions from connecting.
ALTER SYSTEM ENABLE RESTRICTED SESSION;

Once sessions are killed, disable the restricted session by:
ALTER SYSTEM DISABLE RESTRICTED SESSION; To kill a session, the syntax is
Alter system kill session "SID,SERIAL#" immediate;


Note-2


If you want to stop new users from connecting, but allow current sessions to continue until they are inactive, you can put the database in QUIESCE mode:


ALTER SYSTEM QUIESCE RESTRICTED;

From the Oracle Database Administrator's Guide:

Non-DBA active sessions will continue until they become inactive. An active session is one that is currently inside of a transaction, a query, a fetch, or a PL/SQL statement; or a session that is currently holding any shared resources (for example, enqueues). No inactive sessions are allowed to become active...Once all non-DBA sessions become inactive, the ALTER SYSTEM QUIESCE RESTRICTED statement completes, and the database is in a quiesced state


Note-3
   
How to find the current session's ID (the session you are connected)
        
select sid, serial# from v$session where audsid = sys_context('userenv','sessionid');

Or, if you're only interested in the sid, not in serial#:

select distinct sid from v$mystat;


Note, the selected sid has nothing to do with the System Identifier.
If dbms_support is installed, the current sid can also be found with its mysid function:
The package is not installed by default. ..../rdbms/admin/dbmssupp.sql will install it.
select dbms_support.mysid from dual;


Note-4

Try trigger on logon

Insted of trying disconnect users you should not allow them to connect.

There is and example of such trigger.

CREATE OR REPLACE TRIGGER rds_logon_trigger
AFTER LOGON ON DATABASE
BEGIN
  IF SYS_CONTEXT('USERENV','IP_ADDRESS') not in ('192.168.2.121','192.168.2.123','192.168.2.233') THEN
    RAISE_APPLICATION_ERROR(-20003,'You are not allowed to connect to the database');
  END IF;
 
  IF (to_number(to_char(sysdate,'HH24'))< 6) and (to_number(to_char(sysdate,'HH24')) >18) THEN
    RAISE_APPLICATION_ERROR(-20005,'Logon only allowed during business hours');
  END IF;
 
END;
/


Note-5


You can schedule a job to identify the stale sessions and kill them.

The below query can be used to identify those sessions which are inactive from last 3 minutes.


select
    a.username, 
    a.machine,
    ROUND(a.LAST_CALL_ET/60) wait_mins,
    a.sid,
    a.serial#,
    b.spid,
    a.process
from    
    v$session a, v$process b
where
    a.paddr=b.addr
and  a.username not in ('SYS','SYSTEM','DBSNMP','SYSMAN')
-- and  a.machine like 'DR%'
-- and    a.username='SCOTT'
-- and    a.machine <> 'production'
and    a.username is not null
and     a.status ='INACTIVE'
and    a.TYPE='USER'
and    a.LAST_CALL_ET > 180
order by
    spid;

To kill the above sessions which are INACTIVE from last 3 minutes, use the following:

DECLARE
  CURSOR c1 IS
select
    a.username,
    a.machine,
    ROUND(a.LAST_CALL_ET/60) wait_mins,
    a.sid,
    a.serial#,
    b.spid,
    a.process
from   
    v$session a, v$process b
where
    a.paddr=b.addr
and  a.username not in ('SYS','SYSTEM','DBSNMP','SYSMAN')
-- and  a.machine like 'DR%'
-- and    a.username='SCOTT'
-- and    a.machine <> 'production'
and    a.username is not null
and     a.status ='INACTIVE'
and    a.TYPE='USER'
and    a.LAST_CALL_ET > 180
order by
    spid;
kill_it c1%ROWTYPE;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO kill_it;
    EXIT WHEN c1%NOTFOUND;
    BEGIN
      EXECUTE IMMEDIATE 'alter system kill session '''||
       kill_it.sid||', '||kill_it.serial#||'''';
    END;
  END LOOP;
  CLOSE c1;
END;
/


Note-6

from http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1419803982336

then just make the query be:
select ..
from v$session
where sid <> ( select sid from v$mystat where rownum=1)
or -- just

SQL> startup force;
:) would have the same effect.



Note-7

Showsql.sql from AskTom site. Just copy and paste the below in /tmp as showsql.sql script. It will come in handy.
Also, notice the last column is showing time in seconds.

column username format a15 word_wrapped
column module format a35 word_wrapped
column action format a15 word_wrapped
column client_info format a35 word_wrapped
column status format a10
column sid_serial format a15
set feedback off
set serveroutput on
set linesize 200


set feedback on
select username, ''''||sid||','||serial#||'''' sid_serial, status , module, action, client_info,
last_call_et
from v$session
where username is not null
/
set feedback off


column username format a20
column sql_text format a55 word_wrapped


set serveroutput on size 1000000
declare
x number;
pid number;
procedure p ( p_str in varchar2 )
is
l_str long := p_str;
begin
loop
exit when l_str is null;
dbms_output.put_line( substr( l_str, 1, 250 ) );
l_str := substr( l_str, 251 );
end loop;
end;
begin
for x in
( select username||'('||sid||','||serial#||
') ospid = ' || process ||
' command = ' || command ||
' program = ' || program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_time,
sql_address, LAST_CALL_ET, sid, paddr
from v$session
where status = 'ACTIVE'
and sid <> ( select sid from v$mystat where rownum = 1 )
and rawtohex(sql_address) <> '00'
and username is not null order by last_call_et )
loop
select max(spid) into pid from v$process where addr = x.paddr;


dbms_output.put_line( '--------------------' );
dbms_output.put_line( x.username || ' dedicated server=' || pid );
dbms_output.put_line( x.logon_time || ' ' ||
x.current_time||
' last et = ' ||
x.LAST_CALL_ET);
for y in ( select sql_text
from v$sqltext_with_newlines
where address = x.sql_address
order by piece )
loop
p( y.sql_text );
end loop;


end loop;
end;
/


set feedback on



Note-8


Note-9


Note-10




No comments:

Post a Comment

Followers