EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

DBMS_Metadata.get_ddl

Secondary Sidebar
DBMS Tutorial
  • DBMS
    • What is DBMS?
    • Introduction To DBMS
    • DBMS ER Diagram
    • DBMS_RANDOM
    • DBMS_XPLAN
    • DBMS join
    • DBMS Functions
    • Data Administrator in DBMS
    • Checkpoint in DBMS
    • DBMS Table
    • Mapping Constraints in DBMS
    • DBMS Canonical Cover
    • DBMS Log-Based Recovery
    • DBMS Multivalued Dependency
    • View Serializability in DBMS
    • DBMS Concepts
    • DBMS Constraints
    • DBMS_Scheduler
    • B+ Tree in DBMS
    • DBMS_LOB
    • dbms entity
    • DBMS Foreign Key
    • DBMS Users
    • DBMS_Metadata.get_ddl
    • Relational Algebra in DBMS
    • DBMS Components
    • DBMS Features
    • DBMS Models
    • DBMS Relational Model
    • Hashing in DBMS
    • DBMS network model
    • Relationship in DBMS
    • ER Model in DBMS
    • Data Models in DBMS
    • Static Hashing in DBMS
    • Advantages of DBMS
    • dbms_output.put_line
    • DBMS Data Dictionary
    • dbms_xplan.display_cursor
    • Normal Forms in DBMS
    • DBMS helps achieve
    • DBMS 3 tier Architecture
    • Relational Calculus in DBMS
    • Serializability in DBMS
    • File Organization in DBMS
    • DBMS Transaction Processing
    • States of Transaction in DBMS
    • Functional Dependency in DBMS
    • Generalization in DBMS
    • Data Independence in DBMS
    • Lock Based Protocols in DBMS
    • Deadlock in DBMS
    • Integrity Constraints in DBMS
    • Concurrency Control in DBMS
    • Validation Based Protocol in DBMS
    • DBMS Locks
    • Normalization in DBMS
    • Transaction Property in DBMS
    • Specialization in DBMS
    • Aggregation in DBMS
    • Types of DBMS
Home Data Science Data Science Tutorials DBMS Tutorial DBMS_Metadata.get_ddl

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.

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (85,938 ratings)

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

This is a guide to DBMS_Metadata.get_ddl. Here we discuss the definition, overviews, Generating create scripts through dbms_metadata package for better understanding. You may also have a look at the following articles to learn more –

  1. DBMS Locks
  2. DBMS Features
  3. DBMS Components
  4. DBMS 3 tier Architecture
Popular Course in this category
SQL Training Program (7 Courses, 8+ Projects)
  7 Online Courses |  8 Hands-on Projects |  73+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

Special Offer - SQL Training Program (7 Courses, 8+ Projects) Learn More