Creation SQL script for DB Link within a database

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#;

4 comments

  1. select dbms_metadata.get_ddl(‘DB_LINK’, ‘DBLINKNAME’, ‘OWNER’) from dual;

    select dbms_metadata.get_ddl(‘DB_LINK’, ‘DBLINKNAME’, ‘PUBLIC’) from dual;

  2. 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#;