Thursday, November 20, 2014

SQL Scripts for Oracle Database Link

In posts Get the source code for Oracle database views and materialized view and Get source code for Oracle database objects: user_source view, we show how to get the SQL scripts that create some database objects.

To get the SQL scripts for existing database links, we can take advantage of dbms_metadata.get_ddl().The following query returns the database links created.

SQL> select  owner, db_link from all_db_links order by owner, db_link;

OWNER                          DB_LINK
------------------------------ ----------------
PROD                           DL_DATA
PROD                           DL_EC1
To get the original SQL scripts that create these database links, we use dbms_metadata.get_ddl() as shown below.
SQL> select dbms_metadata.get_ddl('DB_LINK', 'DL_EC1') from dual;


  CREATE DATABASE LINK "DL_EC1"    USING '(description=(address=(protocol=TCP)

No comments: