To know which user delete data - Ask TOM (2024)

Thanks for the question, kiran.

Asked: April 24, 2017 - 6:19 pm UTC

Last updated: April 25, 2017 - 8:57 am UTC

Version: oracle 11g

Viewed 1000+ times

Hi

We are doing data warehouse project know, after run the job through ETL it showing data loaded, on the next day there was no data in table. Now we want to know which user deletes data, One thing but we are not a DB team.QA team

Unless you have some strategies in place to foresee this, its not easy because there are all sorts of way to delete data (truncate, drop, direct mode operations) all of which might not be logged in sufficient detail to detect the *who* after the fact.

Ultimately...it means you've got a security problem in your database.

You could explore a few options:

1) flashback - see what kind of transactions were done against the table - sample below

SQL> SELECT deptno, dname, 2 VERSIONS_STARTTIME 3 ,VERSIONS_XID 4 ,VERSIONS_OPERATION 5 FROM dept 6 VERSIONS BETWEEN 7 TIMESTAMP SYSTIMESTAMP - INTERVAL '20:00' MINUTE TO SECOND 8 AND SYSTIMESTAMP 9 WHERE deptno = 10; DEPTNO DNAME VERSIONS_STARTTIME VERSIONS_XID V---------- -------------- ------------------------ ---------------- - 10 UNKNOWN 03-SEP-08 11.53.45 PM 0200100060040000 U 10 MONEY GRABBERS 03-SEP-08 11.53.36 PM 0600050065040000 U 10 FINANCE 03-SEP-08 11.53.24 PM 09000D001D050000 U 10 BEAN COUNTERS 03-SEP-08 11.53.12 PM 01001A00EA030000 U 10 ACCOUNTING

then the transaction ID can be used to find an UNDO_SQL which might yield some info.

2) LogMiner to mind the archived redo logs for transactions

3) go back into v$active_session_history or dba_hist_active_sess_history to see if there were any long running deletes.