2 Replies Latest reply: Feb 15, 2013 10:41 AM by TSharma-Oracle RSS

    Help understanding Materialized views

    JackBox
      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
          >
          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
            1) Drop Materialized view logs
            2) Drop Materialized views
            3) Stop or remove any jobs refreshing Materialized views OR drop any refresh group.