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?
I don't know how do it right, but I did it simply:
create materialized view log on tst_ords
with primary key, sequence
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
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.
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.
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.
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.