Re: Profile limit concurrent sessions for a user not limiting as expected

The RAC thing makes sense. If your logon attempt is checking v$session then every instance can get the limit; and the fact that you use "alter system" rather than alter database to set resource_limit to TRUE hints at the idea it's an instance-level behaviour; and the pattern you reported suggests that the first failure was trying to connect to the same instance as the first two, then connected to the second instance when the application retried..

Be careful with setting sessions_per_user to 1. You may find that when you (or Oracle internally) query a gv$ view the query crashes with ora-12850 (Could not allocate slaves on all specified instances) as a side effect of how RAC starts up PX processes to execute gv$ (and many x$) result sets. This could also have an impact on your use of datapump (as in, it will crash).

Of course you're okay so long as the users with that profile aren't allowed to query gv$, run datapump, or run queries or DML in parallel.

Regards
Jonathan Lewis

On Wed, 17 Jan 2024 at 21:14, Jack van Zanen <jack_at_vanzanen.com> wrote:

> I think it is because the limit is per node and I have a 2 node RAC so
> setting it to 2 actually limits the user to 4
> setting this to 1 limits them to 2.
>
>
> Need to do some more testing, but that is what I think is happening
>
>
>
> Jack van Zanen
>
>
> -------------------------
> This e-mail and any attachments may contain confidential material for the
> sole use of the intended recipient. If you are not the intended recipient,
> please be aware that any disclosure, copying, distribution or use of this
> e-mail or any attachment is prohibited. If you have received this e-mail in
> error, please contact the sender and delete all copies.
> Thank you for your cooperation
>
>
> On Wed, Jan 17, 2024 at 8:40 PM Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>> It certainly sounds wrong. Have you (if you're allowed) checked what
>> happens if you try to get 3 SQL*Plus (or similar) sessions with direct
>> connections. I'm wondering if it's something to do with connection pooling
>> (are the other two sessions still alive, or has one of them - for example -
>> been flagged in v$session as "sniped"), or maybe something to do with the
>> way that the application code handles exceptions and the connect mechanism
>> it uses. Are the three sessions (v$sessions) associated with three
>> difference processes (v$process), or has one process managed to spawn
>> multiple sessions - maybe something like that could bypass the profile
>> limit.
>>
>> Regards
>> Jonathan Lewis
>>
>>
>>
>>
>> On Wed, 17 Jan 2024 at 02:58, Jack van Zanen <jack_at_vanzanen.com> wrote:
>>
>>> Hi
>>>
>>>
>>> We have an application and want to limit the number of sessions a user
>>> can open so are experimenting with the profile and setting "Concurrent
>>> sessions(Per user)" set to 2
>>>
>>> When I start the application I can see one session, so far so good. I
>>> can start another one and that works fine too..However on the third one is
>>> where things do not behave as I expected. I correctly get the pop up that
>>> the sessions have exceeded the value for concurrent max sessions. But when
>>> I click "ok" I get an application logon error popup. When I click that it
>>> starts a third session and logs into the application just fine and leaves
>>> me with 3 working sessions in the database for my username. I was hoping to
>>> get just the error popup and that would be it.
>>>
>>> Can anyone explain under what conditions does oracle allow me to have 3
>>> sessions when my max is set to 3?
>>>
>>> database 12.2
>>>
>>> Regards
>>>
>>>
>>> Jack van Zanen
>>>
>>>
>>> -------------------------
>>> This e-mail and any attachments may contain confidential material for
>>> the sole use of the intended recipient. If you are not the intended
>>> recipient, please be aware that any disclosure, copying, distribution or
>>> use of this e-mail or any attachment is prohibited. If you have received
>>> this e-mail in error, please contact the sender and delete all copies.
>>> Thank you for your cooperation
>>>
>>

--
http://www.freelists.org/webpage/oracle-l