Andrew Fraser DBA

Drop and create database links at materialized view refresh time

Materialized views work ok even when their dependent database link is dropped from under them. This can be used as a security improvement if you don’t want database links lying around during the day outside the refresh window.

Script used to test (with complete refresh) was:

create database link temp_link connect to user identified by password using '\\server\database' ;
create materialized view af1 refresh complete on demand as select * from mytable@temp_link where rownum < 2 ;
create materialized view af2 refresh complete on demand as select * from mytable@temp_link where rownum < 2 ;
select count(*) from af1 ;

drop database link temp_link ;
select count(*) from af1 ;

create database link temp_link connect to user identified by password using '\\server\database' ;
select count(*) from af1 ;
exec dbms_mview.refresh( list => 'af1 , af2' )
select count(*) from af1 ;

drop database link temp_link ;
select count(*) from af1 ;