Generate DDL for all Database Links & Indexes

Below script is going to generate DDL for DBLINK with values instead of password. So that you can create links without knowing the password.

set pagesize 0

set long 90000

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

/

The following can be used to create a script for DB Links without password.

The SQL script for creating the DB Link would need to be run the user who owns the DB Link if it’s not a PUBLIC.

SELECT 'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)

||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME||chr(10)

||'connect to ' || L.USERID || ' identified by '

||L.PASSWORD||' using ''' || L.host || ''''

||chr(10)||';' TEXT

FROM sys.link$ L, sys.user$ U

WHERE L.OWNER# = U.USER#;

Script to generate Index DDL from a table with DBMS_METADATA.GET_DEPENDENT_DDL

Here is a simple script to generate the DDL code for all indexes in a particular table.

set heading off;

set echo off;

Set pages 999;

set long 90000;

SELECT DBMS_METADATA.GET_DEPENDENT_DDL('INDEX',UPPER('&table_name'),UPPER('&schema_name'))

from dual

/

set heading on;

set echo on;