how to check number of users and session that can connect to the database?

Hi Experts,
I want to check the maximum number of user and session that can connect to the database?
please help me on this?
danieshAsked:
Who is Participating?
 
schwertnerConnect With a Mentor Commented:
It is not so easy!

1. every session according Oracle can consume up to 5 MB. So can you imagine
the amount of the RAM you need

2. In many application servers ONE Oracle user can be used to open a pool of connection
for instance 20-30

So use

select count(*) from  v$session;

this will give you the actual number of sessions.


3. If you need to analyze WHO is connected use the columns in the view v$seesion:
SQL> descr v$session;
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------------
 SADDR                                              RAW(4)
 SID                                                NUMBER
 SERIAL#                                            NUMBER
 AUDSID                                             NUMBER
 PADDR                                              RAW(4)
 USER#                                              NUMBER
 USERNAME                                           VARCHAR2(30)
 COMMAND                                            NUMBER
 OWNERID                                            NUMBER
 TADDR                                              VARCHAR2(8)
 LOCKWAIT                                           VARCHAR2(8)
 STATUS                                             VARCHAR2(8)
 SERVER                                             VARCHAR2(9)
 SCHEMA#                                            NUMBER
 SCHEMANAME                                         VARCHAR2(30)
 OSUSER                                             VARCHAR2(30)
 PROCESS                                            VARCHAR2(12)
 MACHINE                                            VARCHAR2(64)
 TERMINAL                                           VARCHAR2(30)
 PROGRAM                                            VARCHAR2(48)
 TYPE                                               VARCHAR2(10)
 SQL_ADDRESS                                        RAW(4)
 SQL_HASH_VALUE                                     NUMBER
 SQL_ID                                             VARCHAR2(13)
 SQL_CHILD_NUMBER                                   NUMBER
 PREV_SQL_ADDR                                      RAW(4)
 PREV_HASH_VALUE                                    NUMBER
 PREV_SQL_ID                                        VARCHAR2(13)
 PREV_CHILD_NUMBER                                  NUMBER
 PLSQL_ENTRY_OBJECT_ID                              NUMBER
 PLSQL_ENTRY_SUBPROGRAM_ID                          NUMBER
 PLSQL_OBJECT_ID                                    NUMBER
 PLSQL_SUBPROGRAM_ID                                NUMBER
 MODULE                                             VARCHAR2(48)
 MODULE_HASH                                        NUMBER
 ACTION                                             VARCHAR2(32)
 ACTION_HASH                                        NUMBER
 CLIENT_INFO                                        VARCHAR2(64)
 FIXED_TABLE_SEQUENCE                               NUMBER
 ROW_WAIT_OBJ#                                      NUMBER
 ROW_WAIT_FILE#                                     NUMBER
 ROW_WAIT_BLOCK#                                    NUMBER
 ROW_WAIT_ROW#                                      NUMBER
 LOGON_TIME                                         DATE
 LAST_CALL_ET                                       NUMBER
 PDML_ENABLED                                       VARCHAR2(3)
 FAILOVER_TYPE                                      VARCHAR2(13)
 FAILOVER_METHOD                                    VARCHAR2(10)
 FAILED_OVER                                        VARCHAR2(3)
 RESOURCE_CONSUMER_GROUP                            VARCHAR2(32)
 PDML_STATUS                                        VARCHAR2(8)
 PDDL_STATUS                                        VARCHAR2(8)
 PQ_STATUS                                          VARCHAR2(8)
 CURRENT_QUEUE_DURATION                             NUMBER
 CLIENT_IDENTIFIER                                  VARCHAR2(64)
 BLOCKING_SESSION_STATUS                            VARCHAR2(11)
 BLOCKING_INSTANCE                                  NUMBER
 BLOCKING_SESSION                                   NUMBER
 SEQ#                                               NUMBER
 EVENT#                                             NUMBER
 EVENT                                              VARCHAR2(64)
 P1TEXT                                             VARCHAR2(64)
 P1                                                 NUMBER
 P1RAW                                              RAW(4)
 P2TEXT                                             VARCHAR2(64)
 P2                                                 NUMBER
 P2RAW                                              RAW(4)
 P3TEXT                                             VARCHAR2(64)
 P3                                                 NUMBER
 P3RAW                                              RAW(4)
 WAIT_CLASS_ID                                      NUMBER
 WAIT_CLASS#                                        NUMBER
 WAIT_CLASS                                         VARCHAR2(64)
 WAIT_TIME                                          NUMBER
 SECONDS_IN_WAIT                                    NUMBER
 STATE                                              VARCHAR2(19)
 SERVICE_NAME                                       VARCHAR2(64)
 SQL_TRACE                                          VARCHAR2(8)
 SQL_TRACE_WAITS                                    VARCHAR2(5)
 SQL_TRACE_BINDS                                    VARCHAR2(5)

SQL>
0
 
schwertnerCommented:
select name,value from v$parameter where name='sessions';
0
 
danieshAuthor Commented:
SQL> select name,value from v$parameter where name='sessions';

NAME
----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
sessions
2205
is this is the value for max sessions and what about max users.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
danieshAuthor Commented:
thanks for the information.
the above result is the number of actual users connect to the database.
SQL> select count(*) from v$session;

  COUNT(*)
----------
        25

the max sessions that can connect to the database is 2205 and the number of users connected to the database is 25.
correct me if i am wrong.
where do we find the max number of users can connect  to the database?
0
 
schwertnerCommented:
This depends on the free RAM (every connection needs up to 5 MB).

So you have to analyze the free RAM of your system.

Theoretically 2205 * 5 = 11025 MB but this is 11 Giga Bytes!!!!

Do you have so much free RAM?

Also every connection uses separate port.

You need 2205 available ports.

Normally the number of connections allowed is between 200 and 500.

An operation mode of Oracle designed to decrease the number of the session
is so called SHARED SERVER mode.
You can find many white papers on the net about this.
0
 
danieshAuthor Commented:
I didnt get you. i just want to know the max number of users and sessions.
0
 
schwertnerCommented:
You can allow 2205 maximum users, but you will never able to have 2205 because this
needs 11GB RAM available ONLY FOR THIS.

You need also 2205 separate ports. But you have not so much ports...

So 2205 maximum users can never be reached on the instance.

Do you understand me?

The main thing you have to understand is:
EVERY connection/user EATS 5 MB RAM AND A PORT ON THE SERVER.
0
 
danieshAuthor Commented:
ok..
I got it..
please let me know show parameter will give me the max number of sessions that can connect to the database.
Is it correct.
0
 
schwertnerCommented:
Calculate the free RAM of the computer (on Windows use Task manager, on linux xosview or other commands).
Divide it by 5 and you will find how many sessions you can have.

Also the portrange is important parameter.

the main question is how to get rid unused and dead sessions in Oracle.
0
 
danieshAuthor Commented:
thanks...
how i will find the numnber of user can connect to the database, the same way as above.
0
 
schwertnerCommented:
Yes, figure out the free amount of RAM and ports.
0
 
danieshAuthor Commented:
prtconf | grep Mem
Memory size: 98304 Megabytes
find the ram size above
check the command.
0
 
danieshAuthor Commented:
i dont how to figure out the freeamount of ports.
0
 
danieshAuthor Commented:
any update on this.
0
 
schwertnerCommented:
In Unix, Linux this is the parameter
port_range. It is configured somewher by Oracle instalaltion.
Read the installation guied for the particulat OS.

Do not miss ALL RAM with FRE RAM. Free RAM is the amount of the RAM after
OS and all application runnig on the box.
0
 
danieshAuthor Commented:
ok...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.