depends on what kind of access application user has (direct access? indirect access?)
application layer auditing, log files, application-server log files?
database layer audit ?
table level audit/etl columns maintained by triggers or maintained by applications ?
Application user has direct access to the tables. We don't have any of the auditing enabled either application level or DB level. Is there any tool from which we can get the update/delete/insert statements from transaction logs and determine from which user ID were these DML statements done
create audit policyexec categories execute status both error type normal;
audit table my_table using policy exec;
-- to extract the audit log records
-- you archive these records:
SELECT file FROM TABLE(AUDIT_ARCHIVE('some_path_to_archive', -2)) t;
-- and extract them to a text file:
CALL AUDIT_DELIM_EXTRACT(NULL, 'some_path_to_extract', 'some_path_to_archive',
'a_value_from_the_previous_select', 'category EXECUTE');