Jerry Jacob's Blog

Oracle – How to find and close connections

Posted in Java/ J2EE, Oracle, Uncategorized by jerlinworld on December 4, 2013

Find the users connected to Oracle

SELECT
  substr(a.spid,1,9) pid,
  substr(b.sid,1,5) sid,
  substr(b.serial#,1,5) ser#,
  substr(b.machine,1,6) box,
  substr(b.username,1,10) username,
  substr(b.osuser,1,8) os_user,
  substr(b.program,1,30) program
FROM v$session b, v$process a
WHERE b.paddr = a.addr
AND type='USER'
ORDER BY spid;

close a specific connection using the above sid and ser#

alter system disconnect session 'sid,ser#' immediate;

Related PL/SQL that I wrote to accomplish this task:

DECLARE
 tmp_sidserial VARCHAR2(15) := '';
 CURSOR cur_user
 IS
   SELECT b.sid, b.serial#
   FROM v$session b
   WHERE TYPE = 'USER' AND b.username = 'JJACOB'
   ORDER BY sid;
BEGIN
   FOR user_rec in cur_user
   LOOP
      tmp_sidserial := user_rec.sid || ',' || user_rec.serial#  ;
      EXECUTE IMMEDIATE 'ALTER SYSTEM DISCONNECT SESSION ''' || tmp_sidserial || ''' IMMEDIATE';
   END LOOP;
END;
/
Advertisements
Tagged with:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: