Solved

Finding How many users can connect to my oracle db

Posted on 2009-04-03
4
905 Views
Last Modified: 2013-12-19
Hi

Please give me a query to find that how many users can copnnect to my Oracle Database.

also give query to find the currently connect number of users.

What the relation between the session , users , processess ????

Regards

Naveen
0
Comment
Question by:NaveenChhibber
  • 2
4 Comments
 
LVL 8

Accepted Solution

by:
morsun earned 125 total points
ID: 24067492
who is connected: select distinct username from v$session;

I don't know of any limitation (excluding OS, hardware etc) for user logins.

Here is the anwer about relations betweens session, process ... http://asktom.oracle.com/pls/asktom/f?p=100:11:1367723241872625::::P11_QUESTION_ID:5671284058977
0
 
LVL 8

Expert Comment

by:morsun
ID: 24067505
Detailed info about logged users:
SELECT s.username, s.program, s.logon_time
FROM v$session s, v$process p, sys.v_$sess_io si
WHERE s.paddr = p.addr(+)
AND si.sid(+) = s.sid
AND s.type = 'USER';

Open in new window

0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 24093928
each user when connects to the database, a session is created for him. if he connects to the same database more than once using then you will find more than one session. Depending on what each session is doing, you may find one or more processes which are  attached to each users session.

In case a session is doing some thing which is normal then you usually find one process running for that session.

In case when you run some query or index rebuilds with parallel hints ( which make use of parallely running threads/processes by oracle )  then you will find more than one process running for the same session depending on how many parallel threads we have specified while
running the query etc...

select * from v$sessions; -- browse thru all the fields available and most of them are self
                                         -- explanatory ; this is to get info about sessions.

select *
from v$process
-- this will contain all processes including oracle background processes running for the database instance.
-- you can join v$session with this view and get all information about the processes running for each session.

select *
from dba_users;
-- this query will give information about all users existing in the database. you can find information
-- like when they were created, account status like password expired, locked, etc and other
-- information as well.

also v$session will have osuser, username etc fields with which you can identify for which user the session is running etc...
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

815 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

10 Experts available now in Live!

Get 1:1 Help Now