Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Finding How many users can connect to my oracle db

Posted on 2009-04-03
4
Medium Priority
?
913 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
3 Comments
 
LVL 8

Accepted Solution

by:
morsun earned 500 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

783 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