Re: Log Miner Does Not Find Deleted Rows

what I mean is looking for delete statement if this is what you looking for and if is still there.

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

• Original Message ----- From: Sinardy To: sbootsma_at_gbrownc.on.ca ; oracle-l_at_freelists.org Sent: Friday, July 15, 2005 10:41 AM Subject: 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