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.
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
Posted by Apun Hiran at 12:47 AM 4 Comments
Labels: drop mview, materialized view, oracle
Subscribe to:
Posts (Atom)