Database Audit Details
-- The audit details of Oracle database are stored in sys.aud$ table.
-- This table is populated when the "audit_trail" parameter is set to 
-- DB.  Some parameters in XE database 

-- Audit parameters in the database.

SELECT num, type, name, value, display_value 
FROM v$parameter
WHERE INSTR(name,'audit')>0
ORDER BY num;

SELECT * FROM sys.aud$
;

SELECT * FROM sys.aud$
WHERE  TO_CHAR(ntimestamp#,'yyyymmdd') = TO_CHAR(sysdate,'yyyymmdd')
;

-- Audit details in the last 14 days

SELECT
  ad.sessionid,
  ad.userid, ad.userhost,
  ad.action#, aa.name  action_description,
  TO_CHAR(ad.ntimestamp#,'mm/dd/yyyy hh24:mi:ss') audit_timestamp
FROM  sys.aud$ ad,
      audit_actions aa
WHERE  ad.action# = aa.action
AND    ad.ntimestamp# > TRUNC(SYSDATE)-14
;


Database Login Analysis

Data Audit by Trigger

Oracle registered trademark of Oracle Corporation.

Last Revised On: May 31st, 2017

  203