This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Feb 15, 2013 10:28 AM by 755501 Go to original post RSS
  • 15. Re: Table changes by timestamp
    rp0428 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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

Legend

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