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.
Comment
Watch Question

Commented:
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)
AS
   v_varchar2   VARCHAR2 (100);
BEGIN
   EXECUTE IMMEDIATE    'ALTER SYSTEM KILL SESSION '''
                     || v_sid
                     || ','
                     || v_serial
                     || '''';
END;
/

Open in new window

Commented:
an improved oracle stored procedure
CREATE OR REPLACE PROCEDURE kill_session (pn_sid NUMBER, pn_serial NUMBER)
AS
   lv_user   VARCHAR2 (30);
BEGIN
   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')
   THEN
      EXECUTE IMMEDIATE    'alter system kill session '''
                        || pn_sid
                        || ','
                        || pn_serial
                        || '''';
   ELSE
      raise_application_error
                (-20000,
                 'Attempt to kill protected system session has been blocked.'
                );
   END IF;
END;
/

Open in new window

Author

Commented:
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.

Commented:
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).
Commented:
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
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2088.htm

Commented:
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

ALTER SYSTEM KILL SESSION 'sid,serial#';

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

Author

Commented:
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.