audit_query_for_SYS_DB_users

set linesize 500

set pagesize 500

COLUMN username FORMAT A10
COLUMN owner FORMAT A10

COLUMN obj_name FORMAT A32

COLUMN os_username FORMAT A16
COLUMN userhost FORMAT A18
COLUMN sql_text FORMAT A300
COLUMN sql_bind FORMAT A300
COLUMN extended_timestamp FORMAT A35

column tm new_value file_time noprint

select to_char(sysdate, 'fmDD-MON-YYYY') tm from dual;

prompt&file_time

spool D:\dblog_LIVE\audit_log_users.log

select username, TO_CHAR(EXTENDED_TIMESTAMP,'fmDd-MM-YYYY HH:MI:SS AM') time, owner, obj_name, os_username, userhost, sql_text, sql_bind fromDBA_audit_trailwhere to_char(extended_timestamp, 'fmDd-MM-YYYY') = to_char(SYSDATE, 'fmDd-MM-YYYY')and username not in ('sys', 'SYS', '/')and username = 'ERP_LIVE'and owner not in ('sys', 'SYS', '/')order by time;

spool off;

EXIT;