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?
 
LowfatspreadConnect With a Mentor Commented:
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
 
sugarfreelessAuthor Commented:
Thanks!
0
All Courses

From novice to tech pro — start learning today.