Restrict multiple sessions from specific terminals
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Restrict multiple sessions from specific terminals

  1. #1
    Join Date
    Apr 2009
    Posts
    4

    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

  2. #2
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    alter profile default limit SESSIONS_PER_USER 4;
    lucky

  3. #3
    Join Date
    Apr 2009
    Posts
    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

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    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..
    this space intentionally left blank

  5. #5
    Join Date
    Apr 2009
    Posts
    4
    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

  6. #6
    Join Date
    Apr 2009
    Posts
    10
    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

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    Quote Originally Posted by B.B View Post
    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.
    this space intentionally left blank

  8. #8
    Join Date
    Nov 2005
    Location
    USA
    Posts
    32
    Quote Originally Posted by hitensharma View Post
    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

  9. #9
    Join Date
    Apr 2009
    Posts
    4
    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

  10. #10
    Join Date
    Nov 2005
    Location
    USA
    Posts
    32
    Quote Originally Posted by hitensharma View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width