Controlling session timeout when using MS ODBC for Oracle

[SJSFoxPro]

Our user accounts are limited to 10 sessions in our Oracle db. This is causing problems for users who are opening multiple sessions of Access and running multiple queries that include many linked tables to the Oracle db (they are running out of sessions). I found that Access was holding inactive sessions for 10 minutes. By editing the registry for the Microsoft Jet 4.0 ODBC settings ConnectionTimeout and QueryTimeout from the default 600 to 30 (Hex), I was able to drop inactive sessions after 1 minute (I expected sooner, like 16 seconds). I found the QueryTimeout property in the query as well, and reduced it to less than 1 minute. I can't seem to drop inactive sessions any before 1 minute and suspect there may be still another setting I've missed that is overriding.

Are there any other properties or settings anywhere else that I am missing or should review?

Thanks for any help you can provide...

 

[oradba101]

Hi, SJSFoxPro

Is ther any particular reason why the user accounts are limited to 10 sessions?

Regards,



William Chadbourne
Oracle DBA

 

[SJSFoxPro]

Just new security requirements... the users are now asking for 30 sessions each. If I can find the settings to drop the inactive sessions in a reasonable amount of time, there should be no need to exceed 10 sessions (certainly not 30 or unlimited).

 

[Turkbear]

Hi,
How, exactly, does limiting the # of sessions enhance security?






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SJSFoxPro]

That was just one of the security parameters that came down from upper management after a meeting. Now that it's causing problems, I'm met with "fix the problem", don't question our decision.

I wish I could answer with all of the reasoning behind the decision, but honestly, I wasn't included in the decision nor asked for my opinion.

I had hoped someone else might have come upon this issue, but perhaps it would just be easier to set sessions back to "unlimited" (might find myself unemployed though)!

My research has turned up setting the ConnectionTimeout and QueryTimeout parameters in the Registry. I suspect through reading today that the last parameter that is keeping my sessions in the pool for 1 minute is the SPTimeout. The ability to configure session pool timeouts became available after MDAC 2.1 SP1. It looks like I need to add a registry entry for my provider to allow configuration of the parameter. (But, I need to test this out to be sure that's the last piece of the puzzle. Plus, see what else will break!)

Thanks anyway.