Hi Experts,
DB - Oracle 11.2.0.4 EE , OS - windows 7
There is a need of restricting concurrent sessions of a user to some value so that a user cannot open more than that concurrent sessions. I Tried it with SESSIONS_PER_USER resource limit but some confusion here.
SQL > alter profile test_profile limit SESSIONS_PER_USER 7;
Test 1 - Opened 7 different sessions , all got connected to DB and 8th failed with below error , so test was successful.
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit
Test 2- Parallel query -
Altered a table to have degree 4.
SQL> alter table tt parallel 4;
Table altered.
SQL> select owner,table_name,degree from dba_tables where owner='HEMANT';
OWNER TABLE_NAME DEGREE
------------------------------ ------------------------------ ----------
HEMANT TT 4
SQL > alter session force parallel query;
SQL > select * from tt;
The explain plan showed table having px operations in the query. from v$session I could see there were 5 session of this user, 4 slaves , 1 coordinator . so we have 2 more sessions left.
while this query is in progress, ran same query again from another session. it allocated only 2 session for it (i.e., 7-5=2) as we have only 2 remaining now so rather than giving error it ran the query with less parallel threads. Test 2 is also successful.
Test 3 - I opened 4 different sessions (no parallel) and just doing autotrace and it failed .
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit
for 4th session -
SQL> set autotrace traceonly explain;
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit
tried the same with parallel query's session also and it failed again. 2 different sessions with 4 parallel threads and again tracing failed.
I cannot copy log here as test was performed on a VM.
can you please let me know if something is wrong in this approach.