11 Replies Latest reply: Jan 19, 2013 4:36 AM by Aman.... RSS

    Oracle logminer - rollback statement

    user632098
      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
          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
            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
              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
                TShrma:
                This is waht I was looking for. Thanks a lot.

                Prem
                • 5. Re: Oracle logminer - rollback statement
                  Aman....
                  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
                    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
                      Thanks Prem!
                      • 8. Re: Oracle logminer - rollback statement
                        Aman....
                        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
                          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
                            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....
                              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....