how to find Maximum users

[vijithv]

How to find the maximum client connections that can b made to a database.I have specified a number at the time of installation of the oracle8i database.

[vinit]

i think this depends on the processes parameter given in y our init.ora . Of these, some are already taken by Oracle background processes.

[vijithv]

i guess i have given the number as 150 but the processes parameters give me as 165!!!!why so?

[julian]

Originally posted by vijithv
How to find the maximum client connections that can b made to a database.I have specified a number at the time of installation of the oracle8i database.

Sessions are limited on users level, not on database level. This is done via profiles. The default is UNLIMITTED. In order to set max number of sessions per user, say 130, you first run:

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;

Then you create a profile, say PROFILE_1:

CREATE PROFILE PROFILE_1 LIMIT
SESSIONS_PER_USER 130
IDLE_TIME X
CONNECT_TIME X...

Run this:

select
profile prof,
resource_name res,
limit lim
from
dba_profiles
order by
1,2
/

[vinit]

Julian - are you talking about MTS ? Coz what i know is that each session takes up a process.

[Subha]

The other 15 is for the backgroung process (like dbwr,lgwr,arch,smon,pmon latch etc ....)

[svk]

According to ORACLE docs :

'processes' is init.ora parameter.
sessions=processes*1.1+5
transactions=sessions*1.1

[julian]

Originally posted by vinit
Julian - are you talking about MTS ? Coz what i know is that each session takes up a process.

No, I am not talking about MTS. I explained how you can control the maximum number of sessions per user in the database.

Of course, processes in the init.ora is sort of a hard limit but if we go in further detail you will see that SEMMNS, that is, the semaphores are also a hard limit.

Here is an intersting formula for setting the number of semaphores (semmns):

"Set to the sum of the PROCESSES parameter for each Oracle database, adding the largest one twice, then add an additional 10 for each database. For example, consider a system that has three Oracle instances with the PROCESSES parameter in their init sid.ora files set to the following values:
ORACLE_SID=A, PROCESSES=100
ORACLE_SID=B, PROCESSES=100
ORACLE_SID=C, PROCESSES=200
The value of SEMMNS is calculated as follows:
SEMMNS = [(A=100) + (B=100)] + [(C=200) * 2] + [(# of instances=3) * 10] = 630

DBAs: be careful! Do not set a large value for SEMMNS, because you then run into the risk of some other user creating so many processes that the kernel process table is totally filled.