asked on

Finding How many users can connect to my oracle db

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

ASKER CERTIFIED SOLUTION

morsun

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

morsun

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

Naveen Kumar

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...