Re: Just wondering about ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit ...

Thanks for all my friends that replied!

Unfortunately, I can't change anything on the app side of the game. That's why I wonder about some code on an before logon trigger.

And thanks Mikhail Velikikh. But I also can't change from dedicated server to shared.

Em qui., 12 de nov. de 2020 às 14:46, Mikhail Velikikh <mvelikikh_at_gmail.com> escreveu:

> Provided you are okay to use shared server and can dedicate a separate
> service for this user, then the following solution could fit the bill. That
> is the closest I can come up with based on your requirements.
>
> 1. alter the dispatchers parameter: I kept the default XDB service and
> introduced another service limited to 3 sessions:
> alter system set
> dispatchers='(PROTOCOL=TCP)(SERVICE=orclXDB)','(PROTOCOL=TCP)(SERVICE=limit)(SESSIONS=3)'
> scope=memory sid='orcl1';
>
> 2. Once I established 3 sessions using that service, all subsequent
> connections are rejected with an error:
> [oracle_at_rac1 ~]$ sqlplus tc/tc_at_rac1:1522/limit
>
> SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 12 17:27:51 2020
> Version 19.9.0.0.0
>
> Copyright (c) 1982, 2020, Oracle. All rights reserved.
>
> ERROR:
> ORA-12516: TNS:listener could not find available handler with matching
> protocol
> stack
>
> 3. then we can just apply retry parameters as usual. I used Easy Connect
> Plus for that:
> [oracle_at_rac1 ~]$ time sqlplus -L tc/tc_at_
> 'rac1:1522/limit?retry_count=3&retry_delay=4'
>
> SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 12 17:28:49 2020
> Version 19.9.0.0.0
>
> Copyright (c) 1982, 2020, Oracle. All rights reserved.
>
> ERROR:
> ORA-12516: TNS:listener could not find available handler with matching
> protocol
> stack
>
>
> SP2-0751: Unable to connect to Oracle. Exiting SQL*Plus
>
> *real 0m12.019s*
> user 0m0.006s
> sys 0m0.011s
>
> 4. in case somebody disconnects in between the retry attempts, your
> session can get through as well.
>
>
>
>
> On Thu, 12 Nov 2020 at 15:24, Luis Claudio Dias dos Santos <
> lsantos_at_pobox.com> wrote:
>
>> Is there way to define a logon timeout?
>>
>> In detail: if some app user is about to get ORA-02391 after a logon
>> attempt the logon process would wait for *n* seconds before receive an
>> ORA-02391.
>>
>> If within this *n *seconds some other sessions logoff the attempt would
>> be successful...
>>
>> Is this possible in declarative way? Or with a LOGON trigger?
>>
>>
>>
>>

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