Re: C# Web Service in IIS Stops Connecting to Oracle (via ADO.NET) Over Time

On Wed, 07 Nov 2007, Temporary_at_yahoo.com wrote:

> My question: assuming that the Web Services'
> Connects (Opens) are all paired with associated
> Disconnect (Closes)

I would isolate and prove this out. Not just by looking at the code but by having the app first connect then you find its session in the database, then have the app close and see if that session actually does go away.

Here is a query you can use (I have this in my SQLPATH and cut-n-pasted it here):

     select t3.SPID, chr(39) || t1.sid || ',' || t1.serial# || chr(39) || ';' kill, 
            t1.username || ':' || t1.machine || ':' || t1.program userurl
            ,to_char(logon_time,'MM/DD/YYYY:HH:MI:SS') || ':' || status, sql_text
     from   v$session      t1
           ,v$sqlarea      t2
           ,v$process      t3     
     where  t1.SQL_ADDRESS  = t2.address (+)
     and    upper(t1.program) not like '%ORACLE%'
     and    t3.ADDR = t1.PADDR
     order by t1.machine,t1.username
     ;

So, do the following:

1. Run the query and keep the output for comparison.
2. Have the app connect and start processing.
3. While the app is executing, run the query and find the connection and make sure this is the connection the app is using.
4. Wait until the app signals it has disconnected.
5. Run the query and see if the previously identified connection is actually gone from the database.

This will prove/disprove your above assumption.

If there are different connection paths, you should do the above test until all ways to connect and disconnect have been tested.

-- 
Galen Boyer