View users who have accessed the database from Oracle Audit Table

DBA can view users who are trying to access the production database through oracle script where they can able to view who are presently logged in / accessed the database.

SELECT OS_USERNAME,
       USERNAME,
       USERHOST,
       TERMINAL,
       TO_CHAR (EXTENDED_TIMESTAMP, 'YYYY/MM/DD HH24:MI:SS') AS EXTENDEDTIME,
       TO_CHAR (LOGOFF_TIME, 'YYYY/MM/DD HH24:MI:SS') AS LFTIME,
       TO_CHAR (TIMESTAMP, 'YYYY/MM/DD HH24:MI:SS') TIMESTAMP,
       ACTION,
       ACTION_NAME,
       SESSIONID,
       ENTRYID,
       STATEMENTID,
       RETURNCODE,
       OWNER,
       OBJ_NAME,
       GLOBAL_UID,
       SCN,
       GRANTEE,
       SQL_BIND,
       SQL_TEXT,
       (SELECT MACHINE
          FROM v$session
         WHERE schemaname = 'SYS' AND ROWNUM = 1)
          AS DHN,
       (SELECT *
          FROM v$version
         WHERE BANNER LIKE 'Oracle%')
          AS DB_VERSION
  FROM SYS.DBA_AUDIT_TRAIL
 WHERE userhost <> '&HOSTNAME' --Enter userhost which you don't need to display
--AND TRUNC (timestamp)   between  '18-JANUARY-2021' and '21-JANUARY-2021'   -- Date range for login activity
and username like 'APPS'
-- and userhost NOT IN ('') --Add user host who have responsibility to access the database
ORDER BY TIMESTAMP

View users who have accessed the database from Oracle Audit Table

Tags