Showing posts with label drop mview. Show all posts
Showing posts with label drop mview. Show all posts

Thursday, January 27, 2011

Drop Materialized View takes a long time

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.