批量刷新远程物化视图的方法(备用)
一、创建远程数据库连接
create database link MDM connect to MDM identified by mdm using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = 172.17.11.1)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SID= MDM)))';
二、创建两个物化视图:
CREATE MATERIALIZED VIEW DEPARTMENT TABLESPACE DAT_ICNF USING INDEX TABLESPACE IDX_ICNF REFRESH FORCE ON DEMAND AS SELECT * FROM MDM.DEPARTMENT@MDM WHERE TREELEVEL = 1 AND STATUS = '0'; CREATE MATERIALIZED VIEW EMPLOYEE TABLESPACE DAT_ICNF USING INDEX TABLESPACE IDX_ICNF REFRESH FORCE ON DEMAND AS SELECT * FROM MDM.EMPLOYEE@MDM WHERE STATUS = '0';
三、创建刷新组
execute DBMS_REFRESH.MAKE(NAME=>'MVTAG', LIST=>'DEPARTMENT,EMPLOYEE', NEXT_DATE=>SYSDATE, INTERVAL=>'/*24:HR*/ SYSDATE + 5/(60*24)'); commit;
四、删除刷新组
--execute dbms_refresh.destroy('MVTAG'); --commit;