9 Replies Latest reply: Jan 1, 2013 1:23 PM by sns RSS

    ORA_ROWSCN - rowdependencies

      i have a metrialized view that can be fast refreshed, built on remote tables. All tables have materialized view log but no rowdependencies clause specified. Is it possible to create materialized view with rowdependencies clause? Is it possible to create a tablespace with Row level track (rodependencies) and then create materialized view on this new tablespace in order to have ora_rowscn related to single records or i have to migrate all the master tables on remote database?

      Thank you very much all
        • 1. Re: ORA_ROWSCN - rowdependencies
          I don't know how do it right, but I did it simply:

          create materialized view log on tst_ords
          with primary key, sequence
          (id_suppl, id_cons)
          including new values
          rename mlog$_tst_ords to mlogo$_tst_ords
          create table mlog$_tst_ords rowdependencies as
          select * from mlogo$_tst_ords where 1=2
          drop table mlogo$_tst_ords

          May be it's a "lite hack", but it works...
          • 2. Re: ORA_ROWSCN - rowdependencies
            One simple question: Why?

            That you can has been shown ... but I wonder what benefit you perceive from having this information.
            • 3. Re: ORA_ROWSCN - rowdependencies
              I need to capture a transaction consistent changes from many mv-logs from one transaction in past (exclusively) till another (inclusive) just near the Now.

              It is a part of system of non-simmetric data propagation, where source systems have a synthetic entities exposed by views with many joined tables, and a consumer system accept them "as is" into a tables.

              By definition of my task, I can not use a usual triggers to capture an events of data changes and can not modify a source tables somehow.
              • 4. Re: ORA_ROWSCN - rowdependencies
                • 5. Re: ORA_ROWSCN - rowdependencies
                  Thank you!
                  There very much letters which I have seen a month ago. But my customer rejects this approach. The maximum what he allow to use is mv-logs...
                  • 6. Re: ORA_ROWSCN - rowdependencies
                    user1704469 wrote:
                    Thank you!
                    There very much letters which I have seen a month ago. But my customer rejects this approach. The maximum what he allow to use is mv-logs...
                    Nothing is impossible for the person who does not have to actually do it!
                    • 7. Re: ORA_ROWSCN - rowdependencies
                      Customers contract for functionality ... they hire experts to tell them how. If you "customer" wants to dictate the methodology tell them to find someone else as they are likely going to be unhappy with anything delivered.

                      ORA_ROWSCN is, in my opinion, the wrong solution and most likely not a solution at all as SCN's in one database mean nothing in another and it wouldn't be hard to hit the same SCN a second time in a number of scenarios.

                      My solution ... create a sequence in one of the two databases and number the rows with/without a timestamp as required.
                      • 8. Re: ORA_ROWSCN - rowdependencies
                        Likely you are right.
                        My customer is an expert of ETL himself, at least, he is sure. He is a high manager with his team - a discussion of his ideas surefire way to end the collaboration.
                        I want try to change his mind but not sure it will turn out - already tried.

                        But about SCN. Commit SCN (ora_rowscn) in current solution needs only for extracting the changes from a number of known mv-logs (of one database) in a transaction consistent way. Commit SCN will never be transfered to another database - they are isolated in their own database.

                        Yes, your proposal seems closer to me. At the beginning of the project I tried to offer something like, but was refused. Nevertheless, yet again I want to carefully study the issue and try to convince the customer do not reinvent the wheel.
                        • 9. Re: ORA_ROWSCN - rowdependencies
                          I have finished study again - CDC looks like a "Tsar Cannon" - it solves a wide range of tasks and takes into account very much circumstances, but gives, ultimately, the same what I can get with MV-logs with commit SCN covered by a special windowing views.
                          Therefore, of course, I'm not sure that I am right, I want to stay in former positions - simply because they are simplest. So long as "dementia and courage"! :)

                          Grateful for the discussion and will be happy to further criticism and concerns.