Skip to Main Content
  • Questions

  • Selective replication using materialized views

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Andrew.

Asked: February 22, 2006 - 3:58 pm UTC

Last updated: March 22, 2006 - 3:57 pm UTC

Version: 9.2.0.5

Viewed 1000+ times

You Asked

We are doing replication from remote database to local database using materialized views. A fairly simple process with mlog$_s for each of the source tables in the remote database and materialized views locally. The problem is, we now want to not replicate deletes from the source tables. So far as I can tell, the triggers that populate the mlog$_s are not available to us to modify so that deletes are not placed in the mlog$_s.

I have considered making the replication job two parts, first delete from the mlog$_s where dmltype$$ = 'D'. But this has problems, like not preventing new delete rows from being inserted into the mlog$_s between the time this transaction ends and the dbms_refresh starts.

I have considered putting a trigger on the mlog$_ to delete the delete rows just as soon as they are inserted, but then I would be attempting to modify an mutating table.

Do you know of a way, other than maybe streams, that would allow me to filter out deletes?

Thanks

and Tom said...

Materialized views were not designed to support that at all. Their entire and only goal in life is "perfect synchronization".

The non-propagation of deletes is not possible to achieve with materialized views, at least not in any sort of supportable fashion (and if the need to rebuild the MV would arise for any reason whatsoever - you would be really out of luck, anything that triggers a complete refresh as well).

Please don't even try to "hack the code" to make this work - there are far too many break points. I rather write "do it yourself" replication before trying to make the builtin Materialized view feature do this.


This is obtainable with streams however, as you can just subscribe to the insert and update "messages" and ignore the deletes.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

Andrew, February 22, 2006 - 4:15 pm UTC

As I expected. I was hoping that there was some switch or option that I had missed in my research. I now know that this is not the case. And the last thing I would even consider trying to do is circumvent the native processes of the database.

Thanks for the help.

Another possibility?

Rory, February 22, 2006 - 4:18 pm UTC

If the deleted records can reside in a separate table, another possibility might be to keep using your materialized views, but separate the delete records out to separate tables. Use before delete triggers to move the data over (the trigger creation can be scripted with not too much work). This has the added benefit of keeping your real tables synced, but also keeping all data available.

So for each table X that you want to track deletes on, create a duplicate table X_DEL, same structure. Before delete trigger copies data to the X_DEL table, materialized views on that can keep it synced to other databases as well.

Just a thought..

Possibility - my 2 cents.

Marcio Portes, February 22, 2006 - 10:44 pm UTC

First of all, this is "do it yourself" sort of. But here we go.
If you change the source tables to have another column say "state" and when you delete some record, actually you don't, you just mark state as 'D'. Now you rename the table to t_physical and create a view with original table name to see only where state <> 'D'. I think your MV will still be around.

export materilized view

vivek.yalla, March 22, 2006 - 8:10 am UTC

i am have 100 gb otlp database i have replicated into 3 database replicated server.i have exported my tables from replication server.after finsishing my work on server in have imported my the .DMP file and i executed my dbms_mview.refrash (fast).in the replication server it is giving only data new inserted in base tables.If i run refresh completed in will get full data because it rewrite query.if i run fast refredh it data in the imported table is disappered.waiting for ur answer.



Tom Kyte
March 22, 2006 - 3:57 pm UTC

did not follow your sequence of events here. insufficient level of detail (but have you worked with support on this as well)

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library