Log Miner Does Not Find Deleted Rows

AIX 5, Oracle 9.2.0.6  

I was given an update statement to run against our TEST database. The equivalent select statement returned 85 rows. Later in the afternoon, the select statement returned no rows. And nobody had run the update statement in the meantime. I used Log Miner to determine who had run an update against the table, and found one row updated by our QA person. What about the other 84 rows? What happened to them? If these rows were updated, the updates should be recorded in the log files and Log Miner should be able to find them. Is Log Miner reliable? Am I missing something?  

Thanks for any ideas. I have pasted in the update statement, and log miner queries immediately below.  

Here is the UPDATE statement (which I never ran):

update swraocc

set swraocc_row_status = 'S'

where swraocc_term_code_entry = '200501'

and swraocc_ROW_STATUS = 'E'

and exists

(

select swraoer_transaction_code

from swraoer

where swraoer_error_code = 'C31'

)

The SELECT corresponding to this UPDATE can be affected by updates to either table swraocc or table swraoer.  

LOG MINER QUERIES
(1) This query shows one update to table SWRAOCC.

SQL> select username, session#, serial#, to_char(timestamp,'yy-mon-dd hh:mi:ss') "time", session_info, sql_redo

  2 from v$logmnr_contents

  3 where sql_redo like '%SWRAOCC%';

SATURN                                 19      19005 05-jul-13 03:27:36

login_username=SATURN client_info= OS_username=nmaniour Machine_name=WORKGROUP\GX270XPMAR04

update "SATURN"."SWRAOCC" set "SWRAOCC_ROW_STATUS" = 'E' where "SWRAOCC_ROW_STATUS" = 'S' and ROWID = 'AAAG3MAAOAABB8OAAC';  

(2) This query shows no update to table SWRAOER

SQL> l

  1 select username, session#, serial#, to_char(timestamp,'yy-mon-dd hh:mi:ss') "time", session_info, sql_redo

  2 from v$logmnr_contents

  3* where sql_redo like '%SWRAOER%'

SQL> /   no rows selected  

(3) This query shows the time of the first and last transaction for
this archived log file. The select that returned 85 rows was made about 1:45 p.m. The same select that returned no rows was run before 4:00p. So I know all activity will be in this log file. This jives with what I determined querying v$archived_log.

SQL> select to_char(min(timestamp),'yyyy-mon-dd hh24:mi:ss'), to_char(max(timestamp),'yyyy-mon-dd hh24:mi:ss')

  2 from v$logmnr_contents;

2005-jul-13 12:00:18 2005-jul-13 16:16:18  

(4) I also spooled the trigger body for all triggers to a file,
performed a search on the file for these two tables, but neither were found. So it appears there is no trigger that updates either of these two tables.

set long 10000

select trigger_body from dba_triggers          

Sam Bootsma

George Brown College

sbootsma_at_gbrownc.on.ca <mailto:sbootsma_at_gbrownc.on.ca>

416-415-5000 x4933  

--
http://www.freelists.org/webpage/oracle-l