How to extract the DB LINKS DDL

February 28, 2023

Script to extract the DB Links DDL with the encrypted password.

SET LONG 20000

SELECT DBMS_METADATA.GET_DDL('DB_LINK',link.db_link,link.owner)

FROM dba_db_links link;

Another way to get the DDL is as below:

SQL> set pages 500
SQL> set long 20000
SQL> set lin 1000

SQL>SELECT 'SELECT dbms_metadata.get_ddl(''DB_LINK'',''' || db_link || ''',''' || owner || ''') as "DB Link DDL" FROM dual;' AS "Query for DDL"
FROM dba_db_links WHERE db_link IN ('&DB1LINK');

SQL> set long 1000000 linesize 1000 pagesize 0 feedback off trimspool on verify off serveroutput on
SQL> select dbms_metadata.get_ddl('DB_LINK','&link_name') from dual;

SELECT 'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME||chr(10)
||'connect to ' || A.USERID || ' identified by '
||L.PASSWORD||' using ''' || A.host || ''''
||chr(10)||';' TEXT
FROM sys.link$ A, sys.user$ U
WHERE A.OWNER# = U.USER#;

A script to generate the DDL for all indexes

set heading off;
set echo off;
Set pages 500;
set long 200000;
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('INDEX',UPPER('&table_name'),UPPER('&schema_name'))
from dual
/
set heading on;
set echo on;