Solved

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

Posted on 2008-06-24
18
953 Views
Last Modified: 2008-09-27
Hi Experts,
I want to check the maximum number of user and session that can connect to the database?
please help me on this?
0
Comment
Question by:daniesh
  • 9
  • 7
18 Comments
 
LVL 47

Expert Comment

by:schwertner
ID: 21854040
select name,value from v$parameter where name='sessions';
0
 

Author Comment

by:daniesh
ID: 21854129
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
 
LVL 47

Accepted Solution

by:
schwertner earned 500 total points
ID: 21854245
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
 

Author Comment

by:daniesh
ID: 21854305
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
 
LVL 47

Expert Comment

by:schwertner
ID: 21854399
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
 

Author Comment

by:daniesh
ID: 21854593
I didnt get you. i just want to know the max number of users and sessions.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 21854699
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
 

Author Comment

by:daniesh
ID: 21855041
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 47

Expert Comment

by:schwertner
ID: 21855375
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
 

Author Comment

by:daniesh
ID: 21855420
thanks...
how i will find the numnber of user can connect to the database, the same way as above.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 21855499
Yes, figure out the free amount of RAM and ports.
0
 

Author Comment

by:daniesh
ID: 21855616
prtconf | grep Mem
Memory size: 98304 Megabytes
find the ram size above
check the command.
0
 

Author Comment

by:daniesh
ID: 21855625
i dont how to figure out the freeamount of ports.
0
 

Author Comment

by:daniesh
ID: 21856113
any update on this.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 21863692
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
 

Author Comment

by:daniesh
ID: 21864387
ok...
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

SSH (Secure Shell) - Tips and Tricks As you all know SSH(Secure Shell) is a network protocol, which we use to access/transfer files securely between two networked devices. SSH was actually designed as a replacement for insecure protocols that sen…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now