Rem
Rem   Create all database links between remote masters (excluding masterdef)
Rem   that are required for replication
Rem
Rem    Oracle corp/Roger Raj    June 1998
connect repadmin/repadmin@&3

set term off
set head off
set echo off
set lines 500
set feedback off
set verify off
set pages 0
col l1 format a300
col n1 format a3 noprint
col dblx format a128 noprint

Rem
Rem Create all public and REPADMIN database links at local master
spool off

spool LOGS/mkdbls.sql
select 'connect repadmin/repadmin@&2' from dual;

select 'drop database link '||dblink||'; ' l1,dblink dblx,'001' n1
from dba_repschema  
where masterdef='N' and dblink != upper( '&2')
union 
select 'drop public database link '||dblink||';' l1, dblink dblx,'002' n1
from dba_repschema  
where masterdef='N' and dblink != upper( '&2')
union
select 'create public database link '||dblink  l1,dblink dblx,'003' n1 
from dba_repschema  
where masterdef='N' and dblink != upper( '&2')
union
select  ' using '||''''||dblink||''''||';'  l1,dblink dblx,'004' n1 
from dba_repschema  
where masterdef='N' and dblink != upper( '&2')
union
select 'create database link '||dblink||' connect to repadmin identified by repadmin;' l1,dblink dblx,
    '005' n1
from dba_repschema  
where masterdef='N' and dblink != upper( '&2')
order by 2,3,1;

select 'connect ods/ods@&2' from dual; 
select 'drop database link '||dblink||'; ' l1,dblink dblx,'001' n1
from dba_repschema  
where masterdef='N' and dblink != upper( '&2')
union 
select 'create database link '||dblink||' connect to ods identified by ods;' l1,
dblink dblx,'002' n2
from dba_repschema  
where masterdef='N' and dblink != upper( '&2')
order by 2,3,1;

Rem
Rem Now, create links from each of the remote master sites to the new site
Rem

select 'connect repadmin/repadmin@'||dblink l1, dblink dblx,'001' n1
from dba_repschema  
where masterdef='N' and dblink != upper( '&2')
union
select 'drop database link '||'&2'||';' l1,dblink dblx, '002' n1
from dba_repschema  
where masterdef='N' and dblink != upper( '&2')
union
select 'drop public database link '||'&2'||';' l1,dblink dblx, '003' n1
from dba_repschema  
where masterdef='N' and dblink != upper( '&2')
union
select 'create public database link '||'&2'l1,dblink dblx,'004' n1
from dba_repschema  
where masterdef='N' and dblink != upper( '&2')
union
select ' using '||''''||upper('&2')||''''||';' l1,dblink dblx,'005' n1
from dba_repschema  
where masterdef='N' and dblink != upper( '&2')
union
select 'create database link '||'&2'||' connect to repadmin identified by repadmin;' l1,
   dblink dblx,'006' n1
from dba_repschema  
where masterdef='N' and dblink != upper( '&2')
union
select 'connect ods/ods@'||dblink l1,dblink dblx,'007' n1
from dba_repschema  
where masterdef='N' and dblink != upper( '&2')
union
select 'drop database link '||'&2'||';' l1,dblink dblx,'008' n1
from dba_repschema  
where masterdef='N' and dblink != upper( '&2')
union
select 'create database link '||'&2'||' connect to ods identified by ods;' l1,
   dblink dblx,'009' n1
from dba_repschema  
where masterdef='N' and dblink != upper( '&2')
order by 2,3,1;

spool off

set termout on
set feedback on
set verify on
-- spool @LOGS/mkdbls.log
prompt  'Make database links  for : &2'
@LOGS/mkdbls.sql
prompt  'Database links  for : &2 created  '
--spool off

