Oracle materialized view replication to postgres using DMS CDC

Dear Customer - Thanks for reaching out to us via AWS re:Post.

To begin with, I would like to inform you that AWS DMS supports only the migration (fullLoad) of materialized views to the target, the materialized views are migrated and created as "base tables" in the DMS target instance.

However, if you like to replicate the REFRESH of materialized views on source to target then it causes issues during CDC, as DMS currently does not support the on-going replication of materialized views.

As a way forward, you can use the base tables of the materialized views for on-going replication of changes to the target Oracle instance. Then if you need to use the materialized views on the Oracle target, you can recreate them using the migrated base tables.

Also, kindly note that AWS DMS doesn't support the ROWID data type or materialized views based on a ROWID column.

[+] https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.Oracle.html#CHAP_Source.Oracle.Limitations

I hope that I was able to address your concerns

instead of migrating materialized view from oracle to postgres target I would suggest create dms task only for underlying tables.

on target postgresql you can create materialized view so that it would have required data coming from underlying tables to populate. https://www.postgresql.org/docs/current/rules-materializedviews.html

If you migrate using DMS it will convert materialized view to table in postgresql and also it wont support migrating changes for this view.