An offshore user contacted me today about an ongoing issue. Apparently the insert statements that his process was performing were disappearing from the table. We stepped through his java code and the code looked fine; the insert was occurring and was successful, no exceptions etc.
We ran a trace on the session and see the insert being executed.
TKPROF: Release 11.2.0.4.0 - Development on Wed Feb 26 11:52:46 2020 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Trace file: PRDA11_ora_8025.trc Sort options: default ******************************************************************************** count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call ******************************************************************************** SQL ID: dgya487q14n43 Plan Hash: 0 insert into ACCTOWN.ACC_CUST (ACCOUNT_NUMBER, STATUS, ACTIVATE_DATE, CUST_ID) values (:1, :2, :3, :4)
However, the row was missing from the table.
My suspicion was that someone or some process was deleting the data from the table. In order to confirm this, I turned on auditing on the table:
AUDIT all ON ACCTOWN.ACC_CUST by ACCESS;
Auditing this database is set to write to the database.
show parameter audit; audit_trail string DB
At this point I asked the user to run the process again and then checked the audit table:
alter session set nls_date_format = 'dd-MON-yy hh24.mi.ss';
select os_username, username, userhost,
timestamp, owner,obj_name,action_name
from dba_audit_trail
where obj_name = 'ACC_CUST'
and action_name not in ('SELECT','SESSION','SESSION REC')
order by timestamp
;
And I was able to confirm that a different process from the same application was deleting the data:
os_username username userhost timestamp owner obj_name action_name ACCTOWN ACCTOWN srsr012 26-FEB-20 10.08.42 ACCTOWN ACC_CUST INSERT kundjo4 ACCTOWN srvr012 26-FEB-20 10.08.46 ACCTOWN ACC_CUST DELETE
After this was confirmed, turn off auditing on the table:
NOAUDIT ALL ON ACCTOWN.ACC_CUST;