This discussion is archived
9 Replies Latest reply: Jan 1, 2013 11:23 AM by 982219 RSS

ORA_ROWSCN - rowdependencies

862721 Newbie
Currently Being Moderated
Hi,
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
    982219 Newbie
    Currently Being Moderated
    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
    damorgan Oracle ACE Director
    Currently Being Moderated
    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
    982219 Newbie
    Currently Being Moderated
    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
    sb92075 Guru
    Currently Being Moderated
    http://www.oracle.com/pls/db112/search?remark=quick_search&word=change+data+capture
  • 5. Re: ORA_ROWSCN - rowdependencies
    982219 Newbie
    Currently Being Moderated
    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
    sb92075 Guru
    Currently Being Moderated
    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
    damorgan Oracle ACE Director
    Currently Being Moderated
    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
    982219 Newbie
    Currently Being Moderated
    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
    982219 Newbie
    Currently Being Moderated
    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.

Legend

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