This discussion is archived
11 Replies Latest reply: Jan 19, 2013 2:36 AM by Aman.... RSS

Oracle logminer - rollback statement

user632098 Newbie
Currently Being Moderated
Logminer question

I am using Oracle 10.2.0.4. I want to use logminer, only problem I have is logminer will not show the actual sql statements used in mycode instead instead its own statements!!!
People who used logminer, can you provide anser to following, If I do following:
Set autocommit off;
Insert into scott …
(I inserted a row in scott table..)
Rollback

For rollback will logminer show a delete statement even though it was not issued.


Thanks,
  • 1. Re: Oracle logminer - rollback statement
    Niket Kumar Pro
    Currently Being Moderated
    may be below link will help you
    http://www.oracle-base.com/articles/9i/logminer-enhancements-9i.php

    Edited by: Niket Kumar on Jan 18, 2013 4:09 AM
  • 2. Re: Oracle logminer - rollback statement
    TSharma-Oracle Guru
    Currently Being Moderated
    It does not matter you commit or rollback, you will always get both sql_redo and sql_undo versions of a statement. For example


    SELECT OPERATION, SQL_REDO, SQL_UNDO
    FROM V$LOGMNR_CONTENTS
    WHERE SEG_OWNER = 'OE' AND SEG_NAME = 'ORDERS' AND
    OPERATION = 'DELETE' AND USERNAME = 'RON';

    The following output would be produced. The formatting may be different on your display than that shown here.

    OPERATION SQL_REDO SQL_UNDO

    DELETE delete from "OE"."ORDERS" insert into "OE"."ORDERS"
    where "ORDER_ID" = '2413' ("ORDER_ID","ORDER_MODE",
    and "ORDER_MODE" = 'direct' "CUSTOMER_ID","ORDER_STATUS",
    and "CUSTOMER_ID" = '101' "ORDER_TOTAL","SALES_REP_ID",
    and "ORDER_STATUS" = '5' "PROMOTION_ID")
    and "ORDER_TOTAL" = '48552' values ('2413','direct','101',
    and "SALES_REP_ID" = '161' '5','48552','161',NULL);
    and "PROMOTION_ID" IS NULL
    and ROWID = 'AAAHTCAABAAAZAPAAN';

    DELETE delete from "OE"."ORDERS" insert into "OE"."ORDERS"
    where "ORDER_ID" = '2430' ("ORDER_ID","ORDER_MODE",
    and "ORDER_MODE" = 'direct' "CUSTOMER_ID","ORDER_STATUS",
    and "CUSTOMER_ID" = '101' "ORDER_TOTAL","SALES_REP_ID",
    and "ORDER_STATUS" = '8' "PROMOTION_ID")
    and "ORDER_TOTAL" = '29669.9' values('2430','direct','101',
    and "SALES_REP_ID" = '159' '8','29669.9','159',NULL);
    and "PROMOTION_ID" IS NULL
    and ROWID = 'AAAHTCAABAAAZAPAAe';
  • 3. Re: Oracle logminer - rollback statement
    TSharma-Oracle Guru
    Currently Being Moderated
    Best link for your understanding

    http://www.progtown.com/topic603823-logminer-does-not-generate-the-information-in-rollback-segments.html

    It will show rollback for sure... see this link for example

    http://oracledoug.com/serendipity/index.php?/archives/1491-Diagnosing-Locking-Problems-using-ASHLogMiner-Part-8.html
  • 4. Re: Oracle logminer - rollback statement
    user632098 Newbie
    Currently Being Moderated
    TShrma:
    This is waht I was looking for. Thanks a lot.

    Prem
  • 5. Re: Oracle logminer - rollback statement
    Aman.... Oracle ACE
    Currently Being Moderated
    TSharma wrote:
    It does not matter you commit or rollback, you will always get both sql_redo and sql_undo versions of a statement. For example


    SELECT OPERATION, SQL_REDO, SQL_UNDO
    FROM V$LOGMNR_CONTENTS
    WHERE SEG_OWNER = 'OE' AND SEG_NAME = 'ORDERS' AND
    OPERATION = 'DELETE' AND USERNAME = 'RON';

    The following output would be produced. The formatting may be different on your display than that shown here.

    OPERATION SQL_REDO SQL_UNDO

    DELETE delete from "OE"."ORDERS" insert into "OE"."ORDERS"
    where "ORDER_ID" = '2413' ("ORDER_ID","ORDER_MODE",
    and "ORDER_MODE" = 'direct' "CUSTOMER_ID","ORDER_STATUS",
    and "CUSTOMER_ID" = '101' "ORDER_TOTAL","SALES_REP_ID",
    and "ORDER_STATUS" = '5' "PROMOTION_ID")
    and "ORDER_TOTAL" = '48552' values ('2413','direct','101',
    and "SALES_REP_ID" = '161' '5','48552','161',NULL);
    and "PROMOTION_ID" IS NULL
    and ROWID = 'AAAHTCAABAAAZAPAAN';

    DELETE delete from "OE"."ORDERS" insert into "OE"."ORDERS"
    where "ORDER_ID" = '2430' ("ORDER_ID","ORDER_MODE",
    and "ORDER_MODE" = 'direct' "CUSTOMER_ID","ORDER_STATUS",
    and "CUSTOMER_ID" = '101' "ORDER_TOTAL","SALES_REP_ID",
    and "ORDER_STATUS" = '8' "PROMOTION_ID")
    and "ORDER_TOTAL" = '29669.9' values('2430','direct','101',
    and "SALES_REP_ID" = '159' '8','29669.9','159',NULL);
    and "PROMOTION_ID" IS NULL
    and ROWID = 'AAAHTCAABAAAZAPAAe';
    It's a good thing to post the source of the information,
    http://docs.oracle.com/cd/B19306_01/server.102/b14215/logminer.htm#i1014404

    *@OP,*
    Though you have mentioned that you got the answer but still, read and bookmark the link given in this reply from Oracle documentation. It covers all that you need to know about Log Miner.

    Aman....
  • 6. Re: Oracle logminer - rollback statement
    user632098 Newbie
    Currently Being Moderated
    Aman:
    My question was specific to use of rollback statement whether sql_redo it will show up as "rollabck" or instead logminer will show a delete staement for the insert statement which I will exceute. TShama showed me an example where it shows it as rlollback in 10.2.0.4. In older version such as Oracle 8i; rollback will not shown as rollabck..That was the reason for my question. Thanks for your response though.
  • 7. Re: Oracle logminer - rollback statement
    TSharma-Oracle Guru
    Currently Being Moderated
    Thanks Prem!
  • 8. Re: Oracle logminer - rollback statement
    Aman.... Oracle ACE
    Currently Being Moderated
    user632098 wrote:
    Aman:
    My question was specific to use of rollback statement whether sql_redo it will show up as "rollabck" or instead logminer will show a delete staement for the insert statement which I will exceute. TShama showed me an example where it shows it as rlollback in 10.2.0.4. In older version such as Oracle 8i; rollback will not shown as rollabck..That was the reason for my question. Thanks for your response though.
    user632098 ,

    This could be easily tested using the link that I had given as it has all the details about the log miner. That's what the point was that you could see that link and set up a test case to clear your doubt.

    Aman....
  • 9. Re: Oracle logminer - rollback statement
    user632098 Newbie
    Currently Being Moderated
    Aman:
    I was able to setup a test and logminer is generating DELETE staements as well as ROLLBACK, i.e,
    ROLLBACK internally issues DELETES (they are shown in log miner) follwoed by ROLLBACK.
  • 10. Re: Oracle logminer - rollback statement
    user632098 Newbie
    Currently Being Moderated
    beacuse oracle shows both delete and rollback statements, question comes is there any way to suprress Oracle internally generated statements and only see the rollback satement in log miner.
  • 11. Re: Oracle logminer - rollback statement
    Aman.... Oracle ACE
    Currently Being Moderated
    I have just got at my hotel after a long travel and I have 10202 and 11203 running in my laptop so I shall test and get back to you for this.

    Aman....

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points