Database Link Creation Script

set pagesize 0
set linesize 30000
set long 500000
set longchunksize 500000
set trimspool on
set feed off


------------Drop Database link

Select 'DROP '||Decode(U.Name,'PUBLIC','public ')||'database link '||Chr(10)
||Decode(U.Name,'PUBLIC',Null, U.Name||'.')|| L.Name||Chr(10) ||';' TEXT
From Sys.Link$ L, Sys.User$ U
Where L.Owner# = U.User#;


------------Create Database link

SELECT '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 values '''
||L.PASSWORDX||''' using ''' || L.host || ''''
||chr(10)||';' TEXT
From Sys.Link$ L, Sys.User$ U
WHERE L.OWNER# = U.USER#;


------------------------CHECK LINK

select 'SELECT INSTANCE_NAME,HOST_NAME FROM V$INSTANCE@' ||Db_Link || ';' from dba_db_links;