How to extract the DB LINKS DDL

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 ''' || || ''''
||chr(10)||';' TEXT
FROM$ A, sys.user$ U

A script to generate the DDL for all indexes

set heading off;
set echo off;
Set pages 500;
set long 200000;
from dual
set heading on;
set echo on;


Popular posts from this blog

How to fix Oracle SQL Developer connection issue "Got minus one from a read call"

Few Important steps of Oracle Database Clone

How to autopopulate user_guid in fnd_user table ( EBS Blank Page)