Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
Is it possible for me to have Oracle MVIEW log in my source database and refresh a Postgresql database MVIEW using this MVIEW log? I know I can query the MVIEW log, I have done that. But since it is not a good practice to query a log, is it even possible to use an MVIEW log from Postgresql just like an Oracle MVIEW uses a log?
If that's not possible, is a dblink from Postgresql to Oracle possible?
The implementation of materialized views in Oracle and Postgres is different. What you're asking would require a level of integration of Postgres into Oracle's proprietary API - to register as a known client MV so that the log correctly updates itself - that I don't believe exists. It would also require a functioning database link to the Oracle database.
This extension looks like it can help Postgres connect to Oracle; I haven't used it myself, but it looks like it should work (although it also appears to have some limitations): https://pgxn.org/dist/oracle_fdw/
At that point, you would need to query the source tables directly, rather than existing MV Logs. Oracle maintains those logs automatically, truncating them or removing rows when it determines that all registered MVs have refreshed a certain set of changes. If there are no registered MVs, then the logs will grow out of control forever. It won't be long before they severely impact the performance of your updates and the Oracle DB (storage requirements, backups, memory, etc.). There are also situations in Oracle where an entire MV log can become invalid and require a complete refresh of the MV to reset. My point is that without the proper client registration, the data in those logs is pretty much useless, or worse than useless: harmful to maintain.
You can use oracle_fdw to connect from PostgreSQL to Oracle and query a materialized view log. I have done that.
Back then I tried to implement replication that way. The problem that led me to give up the project was that the Oracle materialized view log is undocumented, and I could not get all the information I needed. But barring that, there should be no problem.
DBMS_MVIEW.PURGE_LOG to purge the materialized view log; this can be called via oracle_fdw. Of course you are right that it doesn't work if another materialized view uses that log.
Apr 23, 2020 at 20:36