Re: Log Miner Does Not Find Deleted Rows

Only 85 records may be still in your online redo log

if you perform alter system switch logfile; for few times than you add the archive log

If you want to know whether someone executed the update your can quickly try this

select text from v$sqlarea where lower(text) like '%update swraocc%';

• Original Message ----- From: Sam Bootsma To: oracle-l_at_freelists.org Sent: Friday, July 15, 2005 3:55 AM Subject: 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

  416-415-5000 x4933

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