[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

How can I write a SQL query to list all users in active session?

Posted on 2008-01-31
9
Medium Priority
?
2,553 Views
Last Modified: 2013-12-19
How do I write a SQL query that can list all the users in Oracle Applications that have an active session? Are there tables that show a session ID that can be joined to fnd_users either directly or indirectly?
0
Comment
Question by:janthonyn
  • 5
  • 3
9 Comments
 
LVL 18

Accepted Solution

by:
Jinesh Kamdar earned 1260 total points
ID: 20788955
SELECT * FROM v$session;
0
 
LVL 18

Assisted Solution

by:Jinesh Kamdar
Jinesh Kamdar earned 1260 total points
ID: 20789975
Sorry, try SELECT * FROM fnd_v$process;
0
 

Author Comment

by:janthonyn
ID: 20790308
When I say "user", mean end user, not database user like APPS or APPSYSPUB, etc
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

Author Comment

by:janthonyn
ID: 20791596
jinesh kamdar,

Your answer was closer to what I need. When I exclude a couple of obvious system names, applxxx and oraxxx (x's substituted for database name), I get usernames as defined by the Windows network through which we connect to our hosted Oracle database. I also connect directly to the database via a separate VPN. I don't see that listed in your query and it seems also to be missing allot of users I know are active now. It would also help if the table had a time logged on or could join to a view or table with that info.

0
 

Author Comment

by:janthonyn
ID: 20791769
jinesh,

I fiddled with the first sql you gave me, and it's closer if I add a few clauses
select from * v$session
where trunc(logon_time)> SYSDATE-1
order by osuser

But it still doesn't see everybody. I know some users that have been logged in all day and don't appear in the output of the query.
0
 
LVL 18

Assisted Solution

by:Jinesh Kamdar
Jinesh Kamdar earned 1260 total points
ID: 20800299
Remove the filter and see if u find them - SELECT * FROM v$session ORDER BY osuser
0
 
LVL 6

Assisted Solution

by:SJT2003A
SJT2003A earned 240 total points
ID: 20903363
May be some of those are not shown are idle state, check if you could list them too. By default it could be seeing only the ones those are really active. Check if there is any active/idle time constraint and include it in the query.
I never used it before, so I can not give you exact query.

Good luck :)
0
 

Author Comment

by:janthonyn
ID: 20966944
Not yet gotten what I'm looking for. Good suggestions though. i'll keep looking.
0
 

Author Closing Comment

by:janthonyn
ID: 31426831
Still looking for the answer. I'll keep looking
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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 shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

591 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