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#;
Creation SQL script for DB Link within a database
Published by
on
4 responses to “Creation SQL script for DB Link within a database”
-
What can we do for Oracle 10g and 11g?
LikeLike
-
select dbms_metadata.get_ddl(‘DB_LINK’, ‘DBLINKNAME’, ‘OWNER’) from dual;
select dbms_metadata.get_ddl(‘DB_LINK’, ‘DBLINKNAME’, ‘PUBLIC’) from dual;
LikeLike
-
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#;LikeLike
-
enikiasis kafon
Creation SQL script for DB Link within a database « Oracle Spin – Flimatech Blog
LikeLike
Alex Lima 
Leave a reply to Rajeev Cancel reply