How to extract the DB LINKS DDL with the password

Here is a simple script to extract the DB Links DDL with the encripted password.

SET LONG 9000 -- to print the complete string

SELECT DBMS_METADATA.GET_DDL('DB_LINK',a.db_link,a.owner) 
FROM dba_db_links a;

7 comments

  1. By using above query,I didn’t get real password details.Hence my LIVE account has locked suddenly.

    Noone is able to provide the password.

    • I do recommend to test it before you execute anything in your production systems at your own risk.

      In the Disclaimer:
      I do not recommend apply any information posted here in your production system without fully test it in your testing systems.

  2. SELECT ‘CREATE ‘
    || DECODE (U.NAME, ‘PUBLIC’, ‘PUBLIC ‘)
    || ‘DATABASE LINK “‘
    || DECODE (U.NAME, ‘PUBLIC’, NULL, U.NAME || ‘.’)
    || L.NAME
    || ‘” 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#;

    — i found this on net dont recall the site but this should work