Recently I had to drop a couple of large Materialized View.
And dropping them was taking a long time, as it tries to drop the data in both source and destination DB. In Source DB it tries to purge the mview log and at destination mview itself.
To accelerate the process I tried truncating the mview tables at destination and also the mview log table at source.
At destination (mview site):
truncate table mview_to_drop;
At source (mview log site):
select master,log_table from dba_mview_logs where master='MVIEW_TO_DROP';
LOG_OWNER MASTER LOG_TABLE
------------ ------------------------------ ------------------------------
SCOTT MVIEW_TO_DROP MLOG$_MVIEW_TO_DROP
truncate table SCOTT.MLOG$_MVIEW_TO_DROP;
Now back at destination site:
drop materialized view SCOTT.MVIEW_TO_DROP;
Materialized view dropped.
This is the fastest way I could find, please let me know if anyone else has any ideas.
Thursday, January 27, 2011
Drop Materialized View takes a long time
Posted by Apun Hiran at 12:47 AM
Labels: drop mview, materialized view, oracle
Subscribe to:
Post Comments (Atom)
4 Comments:
You may find the following Metalink Link pretty useful as well.
Materialized View Refresh : Log Population and Purge [ID 236233.1]
Thanks Ritzy,
Its indeed a very useful document, related to the architecture and the way mviews functions.
Regards
Apun
I have been solving the same problem. In my case was the solution in droping all indexes on MV before droping the MV itself.
Sorry for no tescase nor idea what might be reason for this behaviour - I don't have time to do it right now. This is just a hint...
Michal
U can also try truncate table 'MV'
then do the drop MV.
I tried and it seemed quite handy.
Post a Comment