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_NAMEselect name from sys.v_$database;set lines 80col TEXT for a70spool &DB_NAME..DB_LINKS.lstselect'--'||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)||';'TEXTfrom sys.link$ L,sys.user$ Uwhere L.OWNER# = U.USER#;spool off
