Database links create a connection from one database to another within Oracle and to third party database objects. There are time such as a clone process or movement of data sets to new servers, which may require the rebuilding of database links. The Below sql script will generate a schell script db_links.sh from the originating database which can be executed in newly created database. NOTE: If you are replacing a test database with production data set, you would first execute below script on you test database, perform your clone/replacement procedure, and finally execute the db_links.sh file generated.
-- rebuild_db_links.sql
--
-- Date: 12-1-2018
-- By: Larry Catt
-- Modified: 12-1-2018
-- Rebuilds database links from within an oracle, generating file db_links.sh
-- which must be launched as the oracle software owner.
--
spool db_links.sh
set serveroutput on
declare
v_txt varchar2(4000);
begin
for rec in(select * from dba_db_links
where owner not in('SYS','PUBLIC'))
loop
dbms_output.put_line('sqlplus / as sysdba <<EOF');
dbms_output.put_line('@conn '||rec.owner);
dbms_output.put_line('drop database link '||rec.db_link||';');
dbms_output.put_line(chr(10));
select dbms_metadata.get_ddl('DB_LINK',rec.db_link,rec.owner) into v_txt from dual;
dbms_output.put_line(v_txt||';');
dbms_output.put_line('EOF');
dbms_output.put_line(chr(10));
end loop;
--PUBLIC
for rec in(select * from dba_db_links
where owner='PUBLIC')
loop
dbms_output.put_line('sqlplus / as sysdba <<EOF');
dbms_output.put_line('drop public database link '||rec.db_link||';');
dbms_output.put_line(chr(10));
select dbms_metadata.get_ddl('DB_LINK',rec.db_link,rec.owner) into v_txt from dual;
dbms_output.put_line(v_txt||';');
dbms_output.put_line('EOF');
dbms_output.put_line(chr(10));
end loop;
--SYS
for rec in(select * from dba_db_links
where owner='SYS')
loop
dbms_output.put_line('sqlplus / as sysdba <<EOF');
dbms_output.put_line('drop database link '||rec.db_link||';');
dbms_output.put_line(chr(10));
select dbms_metadata.get_ddl('DB_LINK',rec.db_link,rec.owner) into v_txt from dual;
dbms_output.put_line(v_txt||';');
dbms_output.put_line('EOF');
dbms_output.put_line(chr(10));
end loop;
end;
/
spool off