We help IT Professionals succeed at work.

close old oracle db connections

ok so the issue is with some old classic code where the db connections are not properly disposed of, we are going through and making that correction.  However, is there a way from vb.net to gather the information from the oracle database, that some connections are over 6 hours old, which clearly indicates that it is abandoned, and then kill that connection without an IIS restart.  I want to create a utility page where it knows the current connections and their age and I can tell it to terninate the old ones.

I am making this high points to see if it will catch someone eye, also I think this will be kind of advanced to guru level.
I tried to make this worth 1500 and could not, I will add what I can when close and grade.
Watch Question

Although I'm not fulfilling your all the requirements to answer this question, BUT my comments may be helpful for you

<<Zones:  Microsoft Visual Basic.Net, PL / SQL>>
---this query will show you login time, status, and username etc
SELECT   t.start_time, s.SID, s.serial#, s.username, s.status, s.schemaname,
         s.osuser, s.process, s.machine, s.terminal, s.program, s.module,
         TO_CHAR (s.logon_time, 'DD/MON/YY HH24:MI:SS') login_time
    FROM v$transaction t, v$session s
   WHERE s.saddr = t.ses_addr
ORDER BY start_time;
--and pass s.SID and s.serial# of the session  you want to kill, to the following procedure
CREATE OR REPLACE PROCEDURE kill_session (v_sid NUMBER, v_serial NUMBER)
   v_varchar2   VARCHAR2 (100);
                     || v_sid
                     || ','
                     || v_serial
                     || '''';

Open in new window

an improved oracle stored procedure
CREATE OR REPLACE PROCEDURE kill_session (pn_sid NUMBER, pn_serial NUMBER)
   lv_user   VARCHAR2 (30);
   SELECT username
     INTO lv_user
     FROM v$session
    WHERE SID = pn_sid AND serial# = pn_serial;
   IF lv_user IS NOT NULL AND lv_user NOT IN ('SYS', 'SYSTEM')
      EXECUTE IMMEDIATE    'alter system kill session '''
                        || pn_sid
                        || ','
                        || pn_serial
                        || '''';
                 'Attempt to kill protected system session has been blocked.'
   END IF;

Open in new window


actually this may be exactly what I need, however could you translate this into really common language for me, as I do not see exactly this v$session on my database. so I will need to be able to ask the dba to have auth to see it, is this a view name a table name, what exactly does v$session stand for please. and thx for the rest as well.  just going to wait for the answer to that and then I am going to give you the points, thanks for the prompt answer.

v$session is a oracle data dictionary view used to see the current sessions.

A record in v$session contains sid and serial#. These numbers can be used to kill a session (alter system kill session).
Username - Name of the Oracle process user
 OS User - Name of the operating system user
 PID - Process ID of the session
 SID - Session ID of the session   <--
 Serial# - Serial# of the session   <--

If you want to know more about the fields in V$SESSION

If you want to access V$SESSION, connect as SYS user and grant select privilege on v$session to the user:
GRANT SELECT ON v_$session TO userName;


To kill sessions, a user needs to have ALTER SYSTEM privilege


values of sid and serial# are are obtained from the columns sid and serial# of v$session.


excellent that is exactly what I needed to know.  I thought it would let me add points but hopefully the A will make it multiply well.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.