This discussion is archived
2 Replies Latest reply: Feb 15, 2013 8:41 AM by TSharma-Oracle RSS

Help understanding Materialized views

JackBox Newbie
Currently Being Moderated
I inherited some db instances that were extensively using Materialized Views. To be honest I have never had to setup or use Materialized Views and I am wondering if my logic on them is correct.

Host Instance
The Materialized View logs live here

Target Instance
The materialized views live here.
When you want your data refreshed you run it here but all of the activity occurs on the host db.

My real issue here is I no longer need the materialized views and I just want to remove them. I have recloned the target instance, so all of the materialized views have been blown away. Do I only need to remove the Materialized View Logs to completely remove them from my environment? Or is their something more that needs to be done.

Thanks
  • 1. Re: Help understanding Materialized views
    rp0428 Guru
    Currently Being Moderated
    >
    My real issue here is I no longer need the materialized views and I just want to remove them. I have recloned the target instance, so all of the materialized views have been blown away. Do I only need to remove the Materialized View Logs to completely remove them from my environment? Or is their something more that needs to be done.
    >
    That's all you need to do. If you used REFRESH GROUPS you can drop those and if you have any DBMS_JOBs that were used to refresh the MVs you won't need those anymore.

    Naturally any code that directly referenced the MV logs or the MVs themselves will need to be modified to remove the references.
  • 2. Re: Help understanding Materialized views
    TSharma-Oracle Guru
    Currently Being Moderated
    1) Drop Materialized view logs
    2) Drop Materialized views
    3) Stop or remove any jobs refreshing Materialized views OR drop any refresh group.

Legend

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