This tip comes from Galina Petrenko, Sr. IT Specialist, TPAS Systems Development, at Towers Perrin, in Voorhees, NJ.
Description: This script produces readable output (backup) for database links in the database. It can be used in the restore/recovery process. Note that passwords for database links are also spooled in a readable format.
col NAME new_value DB_NAME
select name from sys.v_$database;
set lines 80
col TEXT for a70
spool &DB_NAME..DB_LINKS.lst
select
'--'
||CHR(10)
||'-- Owner is ' || U.NAME
||CHR(10)
||'-- DB Link is'|| L.NAME
||CHR(10)
||'--'
||CHR(10)
||'create '||DECODE(U.NAME,'PUBLIC','public')||' database link '
||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME
||chr(10)
||'connect to ' || L.USERID || ' identified by
'''||L.PASSWORD||''' using ''' || L.host || ''''
||chr(10)||';'
TEXT
from sys.link$ L,
sys.user$ U
where L.OWNER# = U.USER#
;
spool off