Generate DDL for all database links with password

3 Feb

Here’s a couple suggestion for a way to generate ddl script for all database links with password (it’s been tested for Oracle 11g and Oracle 10g):

 

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

 

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

 

You can copy the output of the script and run it directly in the database. If you are going to generate ddl for only one database links – you use this method:

SELECT DBMS_METADATA.GET_DDL(‘DB_LINK’,’db_link_name’,’USER’) from dual

Advertisements

One Response to “Generate DDL for all database links with password”

  1. a reader July 20, 2016 at 11:59 pm #

    Fixed:
    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
    || ‘”;’
    TEXT
    FROM sys.link$ L, sys.user$ U
    WHERE L.OWNER# = U.USER#;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: