Solved

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

Posted on 2007-03-23
3
298 Views
Last Modified: 2013-12-19
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
0
Comment
Question by:sugarfreeless
3 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 250 total points
ID: 18780873
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
 
LVL 3

Author Comment

by:sugarfreeless
ID: 18781120
Thanks!
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Suggested Solutions

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup

840 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