Restrict multiple sessions from specific terminals

Hi..

We are using oracle 10.0.2g and os is Win XP. Client are also using 10g & WinXP.

I want to restrict users from creating more than 2 sessions from some terminals (client machines).

Please let me know how I can acheive this.

rgds,
Hiten

alter profile default limit SESSIONS_PER_USER 4;

Hi lucky..

thanks for quick reply.
But I guess it will restrict sessions oracle user wise. I want to restrict the sessions from particular computer/client machine..

Hiten

Are they in the same subnet? Do you have a list of the terminals. You can always write a system level login trigger and then scan for whether or not there is a restriction for the terminal and how many sessions exist from that terminal. If a client exceeds the limit throw an error, which should abort the newest connection..

They are in same subnet. I have written a procedure which upon login checks how many connections are made from particular terminal. If the connections exceeds limit, then it displays the message and raise the error.

But there is one problem in this case. If a user is connected from particular terminal and it is disconnected due to network problem, the user is still shown in v$session. So next time user tries to login, it shows error that the user is already connected so can not have more than 1 connection.

Somebody suggested to kill the sessions depending upon idle time but we kill idle sessions which are idle for more than 30 minutes.

Is there any way to find the sessions which are dead/disconnected ? Just looking at status and Idle time wont work.

Regards,
Hiten

Hiten

You can put the following check condition in your procedure.

select status from v$session;

if the status returned <> 'ACTIVE' then there are no active sessions.

You can incorporate this check in addition to the others that are already present in your proc.

Regards

Barun

Quote:

---

Originally Posted by B.B

Hiten

You can put the following check condition in your procedure.

select status from v$session;

if the status returned <> 'ACTIVE' then there are no active sessions.

You can incorporate this check in addition to the others that are already present in your proc.

Regards

Barun

---

Someone session can be inactive and yet still valid. There maybe something that you can put in the listener that will mark a connection as dropped sooner. Try tahiti.oracle.com to see what that the options are.

Quote:

---

Originally Posted by hitensharma

They are in same subnet. I have written a procedure which upon login checks how many connections are made from particular terminal. If the connections exceeds limit, then it displays the message and raise the error.

But there is one problem in this case. If a user is connected from particular terminal and it is disconnected due to network problem, the user is still shown in v$session. So next time user tries to login, it shows error that the user is already connected so can not have more than 1 connection.

Somebody suggested to kill the sessions depending upon idle time but we kill idle sessions which are idle for more than 30 minutes.

Is there any way to find the sessions which are dead/disconnected ? Just looking at status and Idle time wont work.

Regards,
Hiten

---

You need to enable DCD(Dead Connection Detection). Refer Doc ID: 601605.1 in metalink

Thanks to all for your support.

Finally adding SQLNET.EXPIRE_TIME=3 in sqlnet did the trick. It killed all disconnected sessions. Further another script which executes after 35 minutes, kill users which are connected and inactive but idle for more than 30 mins.

But I am still wondering if there is any way to check DCD connections from v$session or some other table.

Regards,
Hiten

Quote:

---

Originally Posted by hitensharma

Thanks to all for your support.

Finally adding SQLNET.EXPIRE_TIME=3 in sqlnet did the trick. It killed all disconnected sessions. Further another script which executes after 35 minutes, kill users which are connected and inactive but idle for more than 30 mins.

But I am still wondering if there is any way to check DCD connections from v$session or some other table.

Regards,
Hiten

---

I do not think we have any other way of checking dead connections. Leave it to oracle's DCD as it is capable of sending packets over network and decide if the connection is really dead.