Using DBMS_METADATA To Get DDL

An easy, fast and simple way to get DDL (Data Definition Language) of objects within the database is using the package DBMS_METADATA. This package will return us a script with the source code. Below we list some of the types you can get.

 

  • TABLE
  • VIEW
  • INDEX
  • PACKAGE
  • PACKAGE_BODY
  • PROCEDURE
  • FUNCTION
  • CLUSTER
  • CONTEXT
  • DB_LINK
  • JAVA_SOURCE
  • LIBRARY
  • MATERIALIZED_VIEW
  • MATERIALIZED_VIEW_LOG
  • OPERATOR
  • SEQUENCE
  • SYNONYM
  • TABLESPACE
  • TRIGGER
  • TYPE
  • TYPE_BODY

 

Usage;

SELECT DBMS_METADATA.GET_DDL(‘OBJECT_TYPE’,’OBJECT_NAME’,’SCHEMA_OWNER’)
FROM DUAL;

 

By way of example

SELECT DBMS_METADATA.GET_DDL(‘VIEW’,’DBA_ROLE_PRIVS’, ‘SYS’)
FROM DUAL;

 

There are some exceptions such as in the case of an object of type tablespace where you do not need to specify schema owner.

SELECT DBMS_METADATA.GET_DDL(‘TABLESPACE’,’SYSTEM’)
FROM DUAL;

 

 

HTH – Antonio NAVARRO

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s