How to Get DB Link DDL in oracle


how to get ddl for db_link in oracle with password, export db_link with password, drop db link, dbms_metadata.get_ddl dblink, get public db link ddl in oracle, drop db link in oracle.

Viewing Information About Database Links

Oracle has three data dictionary views listed below, which help us to find the information about DB links.

View NamePurpose
DBA_DB_LINKSShow the list of all DB Links from the Database.
ALL_DB_LINKSShow the list of all Database links associated with the currently connected user.
USER_DB_LINKSShow the list of all DB Links owned by the connected user.

Extract DDL for oracle DB links

Using the below command we can get DDL from DB links, execute the output of the command.

SQL> set pages 999
SQL> set long 90000
SQL> set lin 120

SQL>SELECT 'SELECT dbms_metadata.get_ddl(''DB_LINK'',''' || db_link || ''',''' || owner || ''') as "Your DB Link DDL here" FROM dual;' AS "Execute below Query for DDL"
FROM dba_db_links WHERE db_link IN ('DB1LINK'); <<-- Enter your DB Link Name

In my case my DB Link name is DB1LINK.

Output from the above query:

Execute below Query for DDL
SELECT dbms_metadata.get_ddl('DB_LINK','DB1LINK','PUBLIC') as "Your DB Link DDL here" FROM dual;

As I executed the above command, it will work perfectly. Check the below screenshot.


If you found this article useful, please write in the comment box, and subscribe to us on YouTube.

You Must Read:

4 thoughts on “How to Get DB Link DDL in oracle

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to Top