Friday, 15 July 2016

Gather user login information from database

How to know who connected to the oracle database? Or when we see "Fatal NI connect error 12170" in alert log, so how to get the further information on database for investigation?

Normally, the user login information may logged in database such as os_username, machine, login the database have a view called "dba_audit_trail" that facilitate for investigation.

 select os_username,
       to_char(timestamp, 'dd-mm-yyyy hh24:mi:ss') as timestamp,
       to_char(logoff_time, 'dd-mm-yyyy hh24:mi:ss') as logoff_time,
  from dba_audit_trail
 where timestamp between
       to_timestamp('13-07-2016 12:00:00', 'dd-mm-yyyy hh24:mi:ss') and
       to_timestamp('13-07-2016 13:00:00', 'dd-mm-yyyy hh24:mi:ss')
 order by os_process;


No comments:

Post a Comment