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

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?
janthonynAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jinesh KamdarCommented:
SELECT * FROM v$session;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jinesh KamdarCommented:
Sorry, try SELECT * FROM fnd_v$process;
0
janthonynAuthor Commented:
When I say "user", mean end user, not database user like APPS or APPSYSPUB, etc
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

janthonynAuthor Commented:
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
janthonynAuthor Commented:
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
Jinesh KamdarCommented:
Remove the filter and see if u find them - SELECT * FROM v$session ORDER BY osuser
0
SJT2003ACommented:
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
janthonynAuthor Commented:
Not yet gotten what I'm looking for. Good suggestions though. i'll keep looking.
0
janthonynAuthor Commented:
Still looking for the answer. I'll keep looking
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.