EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials DBMS Tutorial DBMS_Metadata.get_ddl
 

DBMS_Metadata.get_ddl

Updated April 1, 2023

DBMS_Metadata.get_ddl

 

 

Introduction to DBMS_Metadata.get_ddl

DBMS_metadata is a utility package that helps display the Data Definition Language and any stored procedures from the data dictionary wherein any object names can be easily obtained is called DBMS_metadata. We can retrieve the metadata from the dictionary either as an XML file or a DDL file that helps create XML objects from scratch. For example, Dbms_metadata.get_ddl is used to get all the DDL entries from the tables, and this output can be entered into SQL script by managing with queries. This is mostly used in Oracle, and we can use it in other servers with slight changes.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Dbms_metadata.get_ddl Overviews

1. Metadata Retrieval: We can retrieve either an object specifically or a collection of object types with this package. Selective options are also available such as user names or any specific table names that can be retrieved easily. Whenever queries return objects, the objects can be parsed easily with the help of the dbms_metadata package. We can also transform the input with the help of StyleSheet language, where XML can be transformed into SQL DDL or any required languages depending on the requirement. This is done with the help of Extensible StyleSheet Language Transformation scripts or XSLT. Retrieval interfaces are available such as OPEN, CLOSE, CONVERT, PUT, etc. There are separate queries available for SQL, such as GET_XML and GET_DDL.

GET_DEPENDENT_XML, GET_GRANTED_XML, GET_DEPENDENT_DDL, GET_GRANTED_DDL are other interfaces for grouped objects.

2. XML Submission: Type of object and transformation parameters must be specified while doing XML submission so that the package can easily find out the object and do the necessary changes automatically. Also, we should specify whether the operation must be carried out or just wants to return the DDL queries. Parse items also should be specified in this criteria, and OPENW, CONVERT, CLOSE, PUT, ADD_TRANSFORM, SET_REMAP_PARAM, SET_TRANSFORM_PARAM, and SET_ITEM_PARAM are the queries to be used while doing XML submission.

Generating create scripts through dbms_metadata package

Create scripts of tables, indexes, packages, and all others are always required by developers, and the dbms_metadata package helps to get all the required scripts easily. Get DDL script is used to retrieve all the required metadata of the table.

DBMS_METADATA.GET_DDL (
object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL,
version IN VARCHAR2 DEFAULT 'COMPATIBLE',
model IN VARCHAR2 DEFAULT 'ORACLE',
transform IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;

We can get the schema of any object in this package using the following script.

select dbms_metadata.get_ddl('USER', du.username) AS DDL_SCRIPT
from dba_users du
where du.username = 'TYPE_USER_NAME'
union all
select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', dtq.username) AS DDL_SCRIPT
from dba_ts_quotas dtq
where dtq.username = 'TYPE_USER_NAME'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', drp.grantee) AS DDL_SCRIPT
from dba_role_privs drp
where drp.grantee = 'TYPE_USER_NAME'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', dsp.grantee) AS DDL_SCRIPT
from dba_sys_privs dsp
where dsp.grantee = 'TYPE_USER_NAME'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', dtp.grantee) AS DDL_SCRIPT
from dba_tab_privs dtp
where dtp.grantee = 'TYPE_USER_NAME'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', drp.grantee) AS DDL_SCRIPT
from dba_role_privs drp
where drp.grantee = 'TYPE_USER_NAME'
and drp.default_role = 'YES'
and rownum = 1;

This can be explained with an example.

CREATE USER "EMPLOYEES" IDENTIFIED BY VALUES 'S:DFDE2EB3729B9D88FC8F7492942D2EA6476BF291FECB0DC56F2A64867F17;T:7D2C79E56B7427C7D5E4E2F209FE4F35524316F9116D15803A5AB1688CAB6DBB1BAC36145075F68C4521E1EA744F2BD53FA8BC08F4BC384B5A0E04D832D49E813BC849BF01C7F38DC780E9BC3C9391E2;8855019455223980'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
PROFILE "PERSONAL";
GRANT "DBA" TO " EMPLOYEES ";
GRANT UNLIMITED TABLESPACE TO "EMPLOYEES ";
ALTER USER " EMPLOYEES " DEFAULT ROLE ALL;
SQL>

Get DDL of a table

Table create script can be written like this.

select dbms_metadata.get_ddl( 'TABLE', 'TABLE_NAME','SCHEMA_NAME' ) from dual

We are creating a new table from this script by giving the table name as Employees.

select dbms_metadata.get_ddl('TABLE', 'OFFER_CLASS','EMPLOYEES') from dual
SQL> set echo off
SQL> set pagesize 0
SQL> set verify off
SQL> set feedback off
SQL> select dbms_metadata.get_ddl('TABLE', 'OFFER_CLASS','EMPLOYEES') from dual;
CREATE TABLE "EMPLOYEES"."OFFER_CLASS"
( "OFFER_ID" VARCHAR2(30 CHAR) NOT NULL ENABLE,
"OFFER_CLASS_SCHEME_CD" VARCHAR2(16 CHAR) NOT NULL ENABLE,
"VALID_FROM_DT" DATE NOT NULL ENABLE,
"VALID_UNTIL_DT" DATE NOT NULL ENABLE,
"OFFER_CLASS_VALUE_CD" VARCHAR2(40 CHAR) NOT NULL ENABLE,
"SOURCE_SYSTEM_CD" VARCHAR2(5 CHAR) NOT NULL ENABLE,
"INSERT_DT" DATE,
"UPDATE_DT" DATE
) SEGMENT CREATION IMMEDIATE
PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255
COLUMN STORE COMPRESS FOR QUERY HIGH NO ROW LEVEL LOCKING NOLOGGING
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
PARALLEL 96
SQL>

Any schema’s tables can be fetched easily in this package that also provides all table’s DDL.

SELECT DBMS_METADATA.get_ddl ('TABLE', table_name, owner)
FROM all_tables
WHERE owner = UPPER('&1')

Index Create Script

It is easy to create scripts for indexes using the dbms_metadata package. This is an example of index creation.

select dbms_metadata.get_ddl( 'INDEX', 'INDEX_NAME','SCHEMA_NAME' ) from dual

Another example of creating scripts of indexes with EMPLOYEES.PK_OFFER_CLASS is as follows.

SQL> set long 100000
SQL> set head off
SQL> set echo off
SQL> set pagesize 0
SQL> set verify off
SQL> set feedback off
SQL> select dbms_metadata.get_ddl('INDEX','PK_OFFER_CLASS',' EMPLOYEES ') from dual;
CREATE UNIQUE INDEX " EMPLOYEES"."PK_OFFER_CLASS" ON "EMPLOYEES"."OFFER_CLASS" ("OFFER_CLASS_SCHEME_CD", "VALID_FROM_DT", "OFFER_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBS_USER" ;
SQL>

Functions – dbms_metadata.get_ddl

All views can be taken from the package with the script’s help.

SELECT DBMS_METADATA.GET_DDL(‘VIEW’,VIEW_NAME) FROM USER_VIEWS

Functions and procedures can also be fetched from the server with the package.

SELECT DBMS_METADATA.GET_DDL('FUNCTION', OBJECT_NAME) FROM USER_PROCEDURES WHERE OBJECT_TYPE = 'FUNCTION'

Also, any package’s DDL script can be fetched easily from the server.

SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE','OBJECT_NAME','SCHEMA_NAME') FROM DUAL
SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY','OBJECT_NAME','SCHEMA_NAME') FROM DUAL

We can set constraints to the table with the help of queries

SQL> SELECT DBMS_METADATA.GET_DEPENDENT_DDL ('REF_CONSTRAINT','OBJECT_NAME','SCHEMA_NAME') from dual

We can grant access to the system or user with the help of the DBMS_metadata package.

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SCHEMA_NAME') from dual
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SCHEMA_NAME') from dual

Conclusion

This function helps sort out all the DDL functions in one go, which is faster than other queries. We can do many applications with this query, and also we can set access and grant permissions in this query. Moreover, constraints can be set clearly in this package.

Recommended Articles

We hope that this EDUCBA information on “DBMS_Metadata.get_ddl” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. DBMS Locks
  2. DBMS Features
  3. DBMS Components
  4. DBMS 3 tier Architecture

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW