Thanks Tom for replying.

Then, what is the benefit of using this?

No one normally leaves system idle for hours after a uncommited transactions, I see less/no benefit from releasing the resources.

People leave terminal and do not log off properly and we reached maximum sessions limit.

Regards,
Lyxx

Dear Tom,

Could you tell me which dictionry view or package supply the information of how long a session is being idle.

Thank you,
Tibor

It means IDLE_TIME will release locks and the sessions would become 'SNIPED' and will remain in v$session. Will the OS script would be the last thing to kill such sessions ?

Those sessions will stay 'sniped' until the client tries to use those connections - so we can return to the client a meaningful error message and then disconnect.

I don't understand that.
"we" is "the Oracle Software", I suppose ?
It usually consists of a server and a client, right ?
The session is not needed to return a meaningful error message in my opinion, this part could also be handled be the Oracle client software - like
ORA-03114: not connected to ORACLE
for example - I think this one is *not* "thrown over the wire", but only raised by client software ?


why do you have these idle sessions - what is the client doing that causes them????? This is the underlying problem we need to solve here.

ok, take this example:
a classical client/server user interface with a human operator working on it.
He/She goes to lunch, forgot to press the "commit" button, and doesn't return in the afternoon because of whatever.

Good luck we have configured idle timeout for her/him ! so transaction is rolled back (after an hour or so).

why the session is hold ?
the client could be informed about the sniping (so that he could raise an appropriate exception when operator touches the application once again) and the session could be killed

would be an improvement in my eyes
should not a be big thing in times of client result cache for example ...

Hi Tom,
If we have an application with connection pooling enabled. Let suppose when the application starts, it automatically creates around 100 connection in the DB pool which has been configured on the basis of normal day load. Now suppose, during early morning or night, those much of users are not connected.
Will the connections present in pool, which are not being used, become inactive and then sniped if IDLE_TIME is enabled?

If yes, how these connections from the pool can be removed?

Thanks,
Abhishek

Hi tom ,
I've followed this post from the beginning but after i set idle time and connect time in profile option to 10 minutes plus adding the parameter SQLNET.EXPIRE_TIME=10 minutes in sqlnet.ora , the problem is the session still marked sniped .
how to tell oracle to disconnect these sniped sessions automatically ?

we have a web based application "A" running on apache tomcat server. The database is Oracle 11.2.3.0. Let's call this database that uses as "P". The web application "A" also views data from a remote database (let's call this database "C") via database links. Database "C" is seeing inactive sessions in v$session for the "user" that database "P" uses via the database link. Database "C" has had to be bounced or their web app had to be restarted in the past because they have had ORA-00020 maximum processes error. People who use/manager database "C" think it is caused because database "P" is leaking connections via the database link it uses to connect to database "C". Hence the DBA for database "P" set the following "max_sessions" and "sessions_per_user" to 50 and "idle_time" to 30min. Setting "idle_time" has caused errors "ORA-02396, ORA-02050" on the web app "A" that uses database "P" because of broken connections being returned to the connection pool after session inactive period. My recommendation was to remove the "idle_time".

I checked with the DBA who administers database "P" and "C" he looked at the Process usage and indicated database "P" on an average used 30% since Januuary until now and he did not see any connection leak issues in database "P". However database "C" seems to be close to 100% dropped down to 60%, 40% and it has been more erratic.

I have set the min and max connection pool parameter to 1 on web application "A". From the tests I have done so far web app "A" created 1 session in v$session for the connection pool and when I ran a functionality on web app "A" that uses objects in database "B" it created a session for it in database "B". I have not seen the sessions increase from the tests I have done in both database "A" and "B". I see v$session entries for these to be inactive and I understand from this trail that having inactive sessions from the connection pool is okay as long as the application is going to be using it and it can use it. Until I shutdown apache/tomcat services for the web app "A" the v$session entries generated from the web app "A" persisted to exist. Now I have the following questions.

1. Is it okay to have v$sessions entries generated via the connection pool in database "P" and "C" which are in inactive state remain until the web app "A" is shutdown ?
My thought is it is okay to leave the v$session entries as such as long as the v$session entries does not keep on increasing on database "P" and "C" and hit the max processes error.

2. I have not seen connection leaks in database "P" but it could also be because of databae bounce done for patches/web application restart when new releases are done. But looking at the "process" usage sent by the DBA for database "P" it does not appear that the processes keeps on increasing. It is steady at 30% approimatelly.
My question is how do I determine on database "C" that connection leaks are happening via the database link tfrom database "P"?
After the DBA set the sessions_per_user and max_session to 50 web app "A" has encountered maximum sessions reached error.

The maximum processes "PROCESSES" has been set to 350 on both database "P" and "C".

3. When running DML using database link do you need to always have to commit to release connections? I read this somewhere. Can you please elaborate on this?

4. Eventhough I dont plan to use it in the application if idle_time is set on the remote database "C" for the database user that database "P" connects to will the transaction via a dblink be rolled back? if there were other transactions made before the database link was used what happens to those? will it be rolled back or will those remain.?

5. Is there any thinking else you need to think of that I need to do to identify connection leaks?

Your expertise, suggestions and feedback have been extremely valuable to me. thank you very much for your suggestions and inputs.

- kls

we have a web based application "A" running on apache tomcat server. The database is Oracle 11.2.3.0. Let's call this database that uses as "P". The web application "A" also views data from a remote database (let's call this database "C") via database links. Database "C" is seeing inactive sessions in v$session for the "user" that database "P" uses via the database link. Database "C" has had to be bounced or their web app had to be restarted in the past because they have had ORA-00020 maximum processes error. People who use/manager database "C" think it is caused because database "P" is leaking connections via the database link it uses to connect to database "C". Hence the DBA for database "P" set the following "max_sessions" and "sessions_per_user" to 50 and "idle_time" to 30min. Setting "idle_time" has caused errors "ORA-02396, ORA-02050" on the web app "A" that uses database "P" because of broken connections being returned to the connection pool after session inactive period. My recommendation was to remove the "idle_time".

I checked with the DBA who administers database "P" and "C" he looked at the Process usage and indicated database "P" on an average used 30% since Januuary until now and he did not see any connection leak issues in database "P". However database "C" seems to be close to 100% dropped down to 60%, 40% and it has been more erratic.

I have set the min and max connection pool parameter to 1 on web application "A". From the tests I have done so far web app "A" created 1 session in v$session for the connection pool and when I ran a functionality on web app "A" that uses objects in database "B" it created a session for it in database "B". I have not seen the sessions increase from the tests I have done in both database "A" and "B". I see v$session entries for these to be inactive and I understand from this trail that having inactive sessions from the connection pool is okay as long as the application is going to be using it and it can use it. Until I shutdown apache/tomcat services for the web app "A" the v$session entries generated from the web app "A" persisted to exist. Now I have the following questions.

1. Is it okay to have v$sessions entries generated via the connection pool in database "P" and "C" which are in inactive state remain until the web app "A" is shutdown ?
My thought is it is okay to leave the v$session entries as such as long as the v$session entries does not keep on increasing on database "P" and "C" and hit the max processes error.

2. I have not seen connection leaks in database "P" but it could also be because of databae bounce done for patches/web application restart when new releases are done. But looking at the "process" usage sent by the DBA for database "P" it does not appear that the processes keeps on increasing. It is steady at 30% approimatelly.
My question is how do I determine on database "C" that connection leaks are happening via the database link tfrom database "P"?
After the DBA set the sessions_per_user and max_session to 50 web app "A" has encountered maximum sessions reached error.

The maximum processes "PROCESSES" has been set to 350 on both database "P" and "C".

3. When running DML using database link do you need to always have to commit to release connections? I read this somewhere. Can you please elaborate on this?

4. Eventhough I dont plan to use it in the application if idle_time is set on the remote database "C" for the database user that database "P" connects to will the transaction via a dblink be rolled back? if there were other transactions made before the database link was used what happens to those? will it be rolled back or will those remain.?

5. Is there any thinking else you need to think of that I need to do to identify connection leaks?

Your expertise, suggestions and feedback have been extremely valuable to me. thank you very much for your suggestions and inputs.

- kls