Script to get DDL of the Database Links
Script to get DDL of the Database Links.
To get DDL of all the DB links in the database
| SELECT ‘alter session set CURRENT_SCHEMA=’||U.NAME||’;’ ||CHR(10)|| ‘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#; |
To get DDL of a specified db link name
Here DB Link name is ‘STGDB_2_DEVDB’ that is given in where clause below.
| SELECT ‘alter session set CURRENT_SCHEMA=’||U.NAME||’;’ ||CHR(10)|| ‘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# and L.name=’STGDB_2_DEVDB’; |
See also