Solved

Finding How many users can connect to my oracle db

Posted on 2009-04-03
4
904 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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.

910 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

24 Experts available now in Live!

Get 1:1 Help Now