0

Currently I am trying to figure out a solution that'll help audit / keep record of Users and their last logon time onto a specific DB. Right now, I am able to query (minus some predicates):

select
   username,
   last_login
from
   dba_users
WHERE  profile = 'USER_PROFILE'
order by
   username;

This obviously works, however it doesn't describe which specific DB was the last logon, as we have multiple. Posting to figure out how to make this happen, if possible.

Thank you for taking your time to read this, hope to figure this out!

The output should look something like:

USER LASTLOG DB
U01 TIME DB1
U01 TIME DB2
U02 TIME DB3
U03 TIME DB3
Connor Low
  • 5,900
  • 3
  • 31
  • 52
Having_A_Blast
  • 1
  • 2
  • Can you update your answer with more details about the multiple databases? What do you mean by "which specific DB"? Are there users in multiple databases? – Connor Low Feb 17 '21 at 18:38
  • Hi Connor, Yes, there are multiple users in multiple DBs. So I would want something like: [USER 1] - [LASTTIMELOGGED] - [DB1] [USER 1] - [LASTTIMELOGGED] - [DB2] [USER 2] - [LASTTIMELOGGED] - [DB1] etc... Does this clear it up a little – Having_A_Blast Feb 17 '21 at 18:45
  • The fact that DB1 has a user named FRED and DB2 also has a user named FRED is completely irrelevant to oracle. DB1's FRED logging on to DB1 is totally unrelated to DB2's FRED logging on to DB2. The view of DBA_USERS in DB1 is exclusive to DB1. So if you issue your query in DB1, it is pointless for the DB to tell you which DB the 'last logon' referred to. The very fact that you are querying in DB1 means it refers to DB1. – EdStevens Feb 17 '21 at 20:40
  • Does this answer your question? [Get top results for each group (in Oracle)](https://stackoverflow.com/questions/134958/get-top-results-for-each-group-in-oracle) – June7 Feb 17 '21 at 22:46

0 Answers0