The following can be used to create a script for DB Links with work on 8i/9i and will contain the password but not in 10g. The SQL script for creating the DB Link would need to be run the user who owns the DB Link if it’s not a PUBLIC.
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 '
||L.PASSWORD||' using ''' || L.host || ''''
||chr(10)||';' TEXT
FROM sys.link$ L, sys.user$ U
WHERE L.OWNER# = U.USER#;
-
Follow
Following
Already have a WordPress.com account? Log in now.
An Oracle Spin by Alex Lima 
What can we do for Oracle 10g and 11g?
select dbms_metadata.get_ddl(‘DB_LINK’, ‘DBLINKNAME’, ‘OWNER’) from dual;
select dbms_metadata.get_ddl(‘DB_LINK’, ‘DBLINKNAME’, ‘PUBLIC’) from dual;
or, try ‘hash values’:
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#;
enikiasis kafon
Creation SQL script for DB Link within a database « Oracle Spin – Flimatech Blog