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

From: Mikhail Velikikh <mvelikikh_at_gmail.com>
Date: Thu, 12 Nov 2020 17:46:32 +0000
Message-ID: <CALe4Hpn3QVxtgcD-4fhkcbNjcdkdb2QS-Wor39f=WBqYHtJZgw_at_mail.gmail.com>

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.

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';

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

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

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

Received on Thu Nov 12 2020 - 18:46:32 CET

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message