Thursday, February 10, 2011

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

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;
from dual
set heading on;
set echo on;

No comments: