Home » SQL & PL/SQL » SQL & PL/SQL » SQL query to view ALL logged on users
SQL query to view ALL logged on users [message #239383] Tue, 22 May 2007 04:15 Go to next message
pgorama
Messages: 13
Registered: May 2007
Junior Member
Hello
I whould like to know how to query all logged on users, not just yourself using "user".

I know there's a lot of useful information in v$session, but I don't understand wich column to look for. In the USERNAME column, I can find my username 3 times, andsome of the rows are inactive and so on.

Maby its some sort of combination between v$session and dba/all_users I should query?

Or maby I just have to select distinct username from v$sessions?

Thanks

[Updated on: Tue, 22 May 2007 04:19]

Report message to a moderator

Re: SQL query to view ALL logged on users [message #239391 is a reply to message #239383] Tue, 22 May 2007 04:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If v$session shows 3 rows with your username, it means you're logged on three times.

What data are you trying to get?
Re: SQL query to view ALL logged on users [message #239397 is a reply to message #239391] Tue, 22 May 2007 04:48 Go to previous messageGo to next message
pgorama
Messages: 13
Registered: May 2007
Junior Member
okej three times, I thought it was three different sessions, like one from jdev, one from sqldev and so on.

The data I'm trying to get is the usernames of every user logged on to the database. Users logged on the moment I query for the data. Not users that have been logged in this week or this day or something like that.

USERNAME
usr1
usr2
usr34
sur98

I tryed "select username from v$session where status = 'active'" but that results in 0 rows, even thoug I´m logged on, so thats probably not it.
Re: SQL query to view ALL logged on users [message #239400 is a reply to message #239397] Tue, 22 May 2007 04:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to activate the audit to know all the users who logged in the past.
Change your audit_trail parameter to DB, restart the database and execute "audit session".
Then you'll find in dba_audit_session all connection and deconnection.

Regards
Michel
Re: SQL query to view ALL logged on users [message #239409 is a reply to message #239397] Tue, 22 May 2007 05:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Try this:
select s.username
      ,s.logon_time
      ,s.program
      ,case when s.status = 'ACTIVE' then 0 
            else s.last_call_et end idle_time
from   v$session s
where  s.type = 'USER';


The ACTIVE column in v$session lists whether a session is currentlt executing a query. I'm very suprised that you managed to get it to return 0 rows.

v$session is described in the docs, as is just about everything else.
Re: SQL query to view ALL logged on users [message #239412 is a reply to message #239383] Tue, 22 May 2007 05:08 Go to previous message
pgorama
Messages: 13
Registered: May 2007
Junior Member
Thanks!
Previous Topic: Append some test with blob data
Next Topic: Separate Alpha-Numeric Column into Alpha and Number
Goto Forum:
  


Current Time: Sat Dec 10 10:49:55 CST 2016

Total time taken to generate the page: 0.18176 seconds