More on Get Source Code for Oracle Database Objects

The following are the three ways to get the source code for Oracle database objects such as views, materialized views, procedures, functions, synonyms, database links etc.
1. For views, we query user_views. This is described in Get the source code for Oracle database views and materialized view
2. For materialized view, we query user_mviews. This is also described in Get the source code for Oracle database views and materialized view.
3. For objects, including views and materialized views, we can use dbms_meta.get_ddl(). See Generate SQL Create Table/View Queries for Existing Tables/Views, SQL Scripts for Oracle Database Link and Hide PL/SQL Scripts For Function or Procedure For example, the following query returns the "create table" statement.

SQL> select dbms_metadata.get_ddl('TABLE', 'TBL_A') from dual;

DBMS_METADATA.GET_DDL('TABLE','TBL_A')
--------------------------------------------------------------------------------

  CREATE TABLE "PROD"."TBL_A"
   (    "ID" NUMBER,
        "VALUE" VARCHAR2(64),
        "VALUE2" VARCHAR2(32)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PROD"

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;

DBMS_METADATA.GET_DDL('DB_LINK','DL_EC1')
--------------------------------------------------------------------------------

  CREATE DATABASE LINK "DL_EC1"    USING '(description=(address=(protocol=TCP)
(host=ec2-xx-xx-xxx-178.compute-1.amazonaws.com)(port=1521))
(connect_data=(sid=XE)))'