-- From time to time database login activities need to be monitored. It is required for both 
-- security and also for monitoring database usage (login and logoff counts).  The 
-- dba_audit_exists and 
-- dba_audit_session
-- have these details and queries show the details.

-- The query can be used for login and Logoff monitoring  

SELECT
 li.sessionid  session_id,
 li.username   user_name,
 li.userhost   host_name,
 li.action_name,
 TO_CHAR(li.timestamp,'mm/dd/yyyy hh24:mi:ss') login_time,
 lo.action_name,
 TO_CHAR(lo.logoff_time,'mm/dd/yyyy hh24:mi:ss') logoff_time,
 li.os_process,
 li.session_cpu
FROM  
  dba_audit_session li,
  dba_audit_session lo
WHERE li.sessionid = lo.sessionid
AND   li.action_name = 'LOGON'
AND   INSTR(lo.action_name,'LOGOFF') > 0; 

-- This query can be used to know detail such as object name and statement ID details.
-- Other details can also be analyzed (statement).


SELECT
 li.scn,
 li.sessionid  session_id,
 li.statementid  statement_id,
 li.username   user_name,
 li.owner,
 li.obj_name object_name,
 li.userhost   host_name,
 li.action_name,
 TO_CHAR(li.timestamp,'mm/dd/yyyy hh24:mi:ss') timestamp,
 li.terminal,
 li.returncode
FROM   dba_audit_exists li
;

-- Other audit table that can be queried to get various database activities by users.


SELECT * FROM dba_audit_trail;

SELECT * FROM dba_audit_object;

SELECT * FROM dba_audit_statement;

SELECT * FROM dba_audit_policies;

SELECT * FROM dba_audit_policy_columns;

SELECT * FROM dba_audit_mgmt_config_params;

SELECT * FROM dba_audit_mgmt_last_arch_ts;

SELECT * FROM dba_audit_mgmt_cleanup_jobs;

SELECT * FROM dba_audit_mgmt_clean_events;