一个大实体化视图的刷新（无法执行全量刷新）。

  Step   Task

 

 

  1

remove summary Mv from refresh group.
exec dbms_refresh.subtract(name=>'SGREPORTS_REPG', list=>'METRIC_DATA_HOURLY');

 

 

 

 2

get the definition of the Mv.
  SQL> set long 100000 pagesize 0
  SQL> select dbms_metadata.get_ddl('MATERIALIZED_VIEW','METRIC_DATA_HOURLY','SGREPORTS') from dual;

 

 

 

 3

drop Mv but preserve the prebuilt table.
drop materialized view METRIC_DATA_HOURLY preserve table;

 

 

 4

 

alter table sgreports.metric_data_hourly truncate partition metric_data_hourly_2009_mar update global indexes;

 

 

 

 5

On master site:
drop and recreate the MV log.
Create table metric_data_hourly_mar as select * from metric_data_hourly partition (metric_data_hourly_2009_Mar);
grant select on  metric_data_hourly_mar to smcrep;
On replication site:
create table metric_hold tablespace LG_DATA
 as select /*+ NOPARALLEL(sgreports.metric_data_hourly_mar) */ *
 from sgreports.metric_data_hourly_mar@RPTS.NILE002A_ZONE01.EAST; 
insert /*+APPEND */ into metric_data_hourly
 select * from metric_hold; 

 

 

 6

 

recreate the Mv with the sql got by step 2.
CREATE MATERIALIZED VIEW sgreports.metric_data_hourly on prebuilt table
REFRESH fast with primary key
AS select *
FROM sgreports.metric_data_hourly@RPTS.NILE002A_ZONE01.EAST;

 

 

7

remove the metric_data_hourlyMv from default refresh group.
exec dbms_refresh.subtract(name=>'metric_data_hourly', list=>'metric_data_hourly');

 

 

8

 

 

add the mv back to the refresh group.
BEGIN
DBMS_REFRESH.ADD (name=>'sgreports_repg',list=>'metric_data_hourly');
END;
/

 

 

9

 

 

refresh manually.
exec dbms_mview.refresh('metric_data_hourly','F');
commit;