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@rac1 ~]$ sqlplus tc/tc@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@rac1 ~]$ time sqlplus -L tc/tc@
'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@xxxxxxxxx> 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?
