List of connected users

[Stephan Schmahl]

Hi,

does anybody know how to get a list of connected users with DOA? It would even be sufficient to know the number of currently active users of the database (tablespace).

Any advice would be helpful.

 

[jpickup]

This is a handy query:

select sid, username, machine, program, status, action
from v$session where username is not null

[This message has been edited by jpickup (edited 07 August 2001).]

 

[Stephan Schmahl]

Thanks for your help. It works, but it seems that the user needs to have a dba role in order to execute this query.

I wanted to implement a floating license restriction with it (only x concurrent logins are allowed, for the x+1 user the access would be denied).

Does there exist a better way to do this or another query where the dba role is not needed?

 

[Ura]

run
"grant select on v$session to user"
from SYS sheme

DBA role is not required

 

[Stephan Schmahl]

Ok that works. But unfortunately I cannot use it because I have to build an installation program and asking for a login as "sys" in order to complete installation would be too insolent. The customer would suppose that a "db spy" or something like that is going to be installed. The same customer doens't want to grant dba role to the required user so I have a great problem now.

Any solutions?

 

[o.germeau]

One other way is to use the DBMS_LOCK package. For each connection, you use ALLOCATE_UNIQUE procedure.
In my application, it's work well for control if a user is connected or not.
I use one oracle user for many user in my application.

 

[MarkR]

If all you want is the number of concurrent users, you can setup a stored function which is owned by a user with DBA privileges that returns an Integer value from a select count(*) query on v$session. Then grant execute on that function to all users. You will get the result and none of the application users need DBA privileges.

I have done something similar to this to allow users to change their own passwords and it works great.