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 Name | Purpose |
| DBA_DB_LINKS | Show the list of all DB Links from the Database. |
| ALL_DB_LINKS | Show the list of all Database links associated with the currently connected user. |
| USER_DB_LINKS | Show 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 NameIn 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”