1 2 Previous Next 18 Replies Latest reply: Feb 15, 2013 12:28 PM by 755501 Go to original post RSS
      • 15. Re: Table changes by timestamp
        rp0428
        >
        Can I manually remove records from th MV log based on a specfic date/time?
        >
        You would remove records that you have 'processed' and no longer need.

        A common method of doing this is to use the SEQUENCE clause when creating the MV log. That sequence value tells you the order that the operations were done.

        Then you process records by the sequence column and then delete those records that have sequence values that you have processed.
        DROP MATERIALIZED VIEW LOG ON SCOTT.EMP
        
        CREATE MATERIALIZED VIEW LOG ON SCOTT.EMP
        WITH ROWID, PRIMARY KEY, SEQUENCE
        (ENAME, COMM)
        INCLUDING NEW VALUES;
        
        update emp set comm = 999
        where empno = 7788;
        
        commit;
        
        update emp set comm = 888
        where empno = 7876;
        
        commit;
        
        update emp set comm = 777
        where empno = 7902;
        
        commit;
        
        select empno, ename, comm, m_row$$, sequence$$, dmltype$$, old_new$$
        from mlog$_emp;
        
        EMPNO     ENAME     COMM     M_ROW$$     SEQUENCE$$     DMLTYPE$$     OLD_NEW$$
        7788     SCOTT          AAAR3xAAEAAAACXAAH     40     U     O
        7788     SCOTT     999     AAAR3xAAEAAAACXAAH     41     U     N
        7876     ADAMS          AAAR3xAAEAAAACXAAK     42     U     O
        7876     ADAMS     888     AAAR3xAAEAAAACXAAK     43     U     N
        7902     FORD          AAAR3xAAEAAAACXAAM     44     U     O
        7902     FORD     777     AAAR3xAAEAAAACXAAM     45     U     N
        You can see that sequences 40/41 were done first, then 42/43, then 44/45. So if you process records with sequence 40, 41, 43, 43 you would then delete those records when you are done with them.

        You need to keep track of what the last sequence# was that you have processed if you leave the records in the table. Also note that because NEW VALUES were included there are TWO records for each operation. So you need to make sure to process/delete the records in pairs.
        • 16. Re: Table changes by timestamp
          755501
          Ok, that makes sense.

          I created the MV log on my table like this:

          CREATE materialized view log on students WITH SEQUENCE,rowid (id,last_name,first_name,middle_name) INCLUDING NEW VALUES

          I updated some middle_name columns in my table and did this select:

          select id, last_name, first_name, middle_name, m_row$$, sequence$$, dmltype$$, old_new$$
          from mlog$_students
          order by sequence$$;

          ID_______LAST_NAME_________FIRST_NAME________MIDDLE_NAME_______M_ROW$$___________________SEQUENCE$$________DMLTYPE$$_________OLD_NEW$$
          46497____Adaiir______________Terry______________- __________________AAAT5QAAEAAAc38AAA_______1_________________ U__________________U
          46497____Adaiiir______________Terry______________Anne______________ AAAT5QAAEAAAc38AAA_______2__________________U__________________N
          1906_____Magee_____________Dusty______________B__________________AAAT5QAAMAAAavnAAB_______3__________________U__________________U
          1906_____Magee_____________Dusty______________Brian_______________AAAT5QAAMAAAavnAAB_______4__________________U__________________N

          I can see the sequence$$ column showing the order in wich the operations were done. Some questions:

          1) When you say you need to process/delete the records in pairs, does the 'M_ROW$$' column keep the same values for each transaction set? I noticed it was the same for the old/new on each transaction. Is that what you would use to guarantee that you processed the log including all the same transaction pairs?

          2) I noticed that my 'OLD_NEW$$' columns are showing a 'U' and 'N' (instead of an 'O' and 'N'). Why?

          3) What is the recommended way to remove the records from the MV_log? I did a simple delete statment below based on the 'sequence$$' column and it seemed to work fine.

          delete from mlog$_students
          where sequence$$ > 4

          Thanks for all the help!

          Edited by: bobmagan on Feb 15, 2013 4:35 AM
          • 17. Re: Table changes by timestamp
            rp0428
            >
            I can see the sequence$$ column showing the order in wich the operations were done. Some questions:

            1) When you say you need to process/delete the records in pairs, does the 'M_ROW$$' column keep the same values for each transaction set? I noticed it was the same for the old/new on each transaction. Is that what you would use to guarantee that you processed the log including all the same transaction pairs?
            >
            That is the ROWID. So if you process the data by ROWID you can delete by ROWID from the log
            >
            2) I noticed that my 'OLD_NEW$$' columns are showing a 'U' and 'N' (instead of an 'O' and 'N'). Why?
            You didn't provide the queries that show what changes you made to the table. 'U' usually means 'update'. You need to test different operations: insert, update, delete, update a column then update it again to another value and then update it again and see what you get.
            >
            3) What is the recommended way to remove the records from the MV_log? I did a simple delete statment below based on the 'sequence$$' column and it seemed to work fine.

            delete from mlog$_students
            where sequence$$ > 4
            >
            How can that work fine? You had four rows and now after the delete you still have four rows. You would normally process the earlier rows first and then delete them. So if those are the only rows in the table you would process 1/2 and maybe 3/4 and then delete them rather than leave them in the table.

            It is just a table like any other table. Delete rows any way you like when you no longer need them. You may need to add an appropriate index on the column you use in the query predicate.
            • 18. Re: Table changes by timestamp
              755501
              That makes sense. thanks.

              One last question (I hope). If I wanted to expand this mv logging to multiple child tables, is there any way to know that all the changes were grouped together?

              ie. A change is made to the master 'emp' table which also resulted in cascading changes to 2 other tables. I want to be able to see that table2, and table3's changes were made from the transaction in table1.
              1 2 Previous Next