Query to find database links(db links) and it's DDLs in a database.

This document describes the details of database links in the database.

Some times we need to find the existing database links in the database and it's DDLs to recreate the database links. Mostly this kind of scenario we get as post refresh (clone) of database.

Please click Here to know how to create database links and what is the difference between private and public database links.

We can find existing database links using below query.

col owner for a15    
col db_link for a45
col username for a16
col host for a30

set lines 500

select * from dba_db_links;

We can find db link DDLs using below query.

set long 900000

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

Thanks for going through this post......