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.
LVL 4
reginabAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

0
sonicefuCommented:
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

0
reginabAuthor 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.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

sonicefuCommented:
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).
0
sonicefuCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sonicefuCommented:
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.
0
reginabAuthor 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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.