3 Replies Latest reply: Feb 22, 2013 5:14 PM by rp0428 RSS

    MV Log capturing transactions

    755501
      I am using oracle 11.2 DB and creatied a MV log on a table to track changes and new/old column values.

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

      Since some of the processes update more that 1 table at a time before doing a commit, I need to be able to capture that these 3 table updates are all part of the same transaction (commit) .

      ie.
      My Process does:
      update table 1 set x = '1';
      update table 2 set y = '2';
      update table 3 set z = '3';
      commit;

      I have been reading that the column xid$$ (transaction id) in the log uniquely identifies the transaction that made the changes to the row. So if I create the log on these 3 tables, will it record the same xid$$ value for changes that were made during the single process?

      thanks
        • 1. Re: MV Log capturing transactions
          rp0428
          >
          I have been reading that the column xid$$ (transaction id) in the log uniquely identifies the transaction that made the changes to the row. So if I create the log on these 3 tables, will it record the same xid$$ value for changes that were made during the single process?
          >
          Don't be afraid of breaking Oracle by actually trying things! (With the obvious exception of manually altering the data dictionary or SYS objects)
          • 2. Re: MV Log capturing transactions
            755501
            Thanks. My initail tests seem to indicate that values are the same. Just wanted someone to confirm if they knew.
            • 3. Re: MV Log capturing transactions
              rp0428
              And now you know. A transaction, once begun, is ended when either a COMMIT or ROLLBACK is executed (either explicitly or implicitly).

              MV log updates are performed as part of a transaction. There could be multiple transactions updating the MV log at the same time but they will have different transaction IDs. The transaction Id is analogous to an SCN.