How to track down logins by username, machine, and schemaname in oracle 9i

Experts,

disclamer: I am new to oracle support so be kind ;)
I need to find how often a specific user logs into the oracle 9i db and from where for a specific schema.

The only thing I have at this point is a sql query running every 5 minutes.
It looks like this:
select username, count(*) from gv\$session group by username order by 2 desc;
-- and --
select username, machine, count(*) from gv\$session group by username, machine order by username, 3 desc;

I have not been able to make the sql work with the schemaname table I receive an error:
sql: select schemaname, username, count(*) from gv$session group by username order by 2 desc;
"ORA-00979: not a GROUP BY expression" with schemaname highlighted
LVL 3
sugarfreelessAsked:
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.

LowfatspreadCommented:
but your simple error is in the group by you are missing the schemaname

select schemaname, username, count(*) from gv$session group by username order by 2 desc;
needs to be...
select schemaname, username, count(*) from gv$session group by schemaname,username order by 2 desc;
somebody in the oracle thread may be able to assist with the actual functioning of the query

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
sugarfreelessAuthor Commented:
Thanks!
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.