I looked in the deleted schema at the "deleted_by" field in eo_del to see who had performed some EO deletions. But all of the values in the field are "BIOTICS_USER", which doesn't help me at all. Why can't you make this be the user_id?


You've run across a limitation of the audit log design, I'm afraid. It works this way on purpose, because it was found to be extremely time-consuming to have the database triggers that populate the deleted schema also figure out who the real user was that was doing the deletion. This was attempted during the design, but had to be abandoned.


The only other thing I can offer you as a work around is that you can query the session log to find out who was logged on at the time that the EO was deleted. Here's how:

select sl.* from session_log sl, audit_log al
where al.table_name = 'EO' and
al.primary_key_id = 166533 and
al.action_code = 'D' and
al.audit_date between sl.session_started and session_ended;

This probably wouldn't hold up in a court of law, but it will at least narrow down the list of suspects. You can do this for anything that was deleted by just changing the table_name and primary_key_id values to whatever you want.