How to Generate table DDL

Hi, in this practice we are going to learn how to generate table DDL, view DDL, Materialized View DDL and user DDL.

You also read below articles:

• DB LINK DDL
• GET TABLESPACE DDL
• GET ROLE DDL

Get more oracle scritps

How do you get DDL of a table in Oracle?

In real time environment some times we need to DDL of an existing table. We can perfo this activity with the help of dbms_metadata.get_ddl package.

Generate table DDL

set long 9999
set pagesize 300 lin 600
set feedback off
set verify off
set trimspool on

select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;

Outpur from above query: Using the below DDL we can create the same structure table on another server.

How do you get DDL of a view in Oracle?

It is same as we get the table DDL, so after litle change in the query you can get the View DDL also. In this step we also given the answare of this question: How can I get DDL of view?

SQL>  select dbms_metadata.get_ddl('VIEW','EMP_DETAILS_VIEW','HR') FROM DUAL;

Outpur from above query: In the below view you can check complete DDL of VIEW.

Get ddl of materialized view in oracle

Using the below query you will get the materialized view DDL.

SQL> select dbms_metadata.get_ddl('MATERIALIZED_VIEW','&M_VIEW_NAME','&OWNER_OF_MVIEW') from dual;

How do you get DDL of a schema in Oracle?

If you realy want to genrate any existing schema DDL to create other schema with same structure use the below command. “How can I get DDL of a user?”

SQL>select dbms_metadata.get_ddl('USER','SCOTT') from dual;

• Instagram
• Facebook
• Twitter
• LinkedIn

• 
  Convert Physical Standby To Snapshot Standby Database
• 
  Create spool file with timestamp in oracle
• 
  Drop Database Manually in Oracle
• 
  How to Add New Disk in ASM DiskGroup
• 
  How to Check Oracle Instance Size
• 
  How to Check Oracle UPTIME AND STARTUP history?