Get DDL of objects in Oracle

Hi,

To get DDL of oracle database objects you can use dbms_metadata.getddl package.

Syntax:

select dbms_metadata.get_ddl('OBJECT_TYPE','OBJECT_NAME','SCHEMA') from dual;


Below is an example how this works.


set heading off;
set echo off;
Set pages 999;
set long 90000;
set lines 180

spool ddl_EMP.sql

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

spool off;

Here is the output of spool file:

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

CREATE TABLE "SCOTT"."EMP"   ("EMPNO" NUMBER(4,0),  "ENAME" VARCHAR2(10), 
 "JOB" VARCHAR2(9),        "MGR" NUMBER(4,0), "HIREDATE" DATE,  "SAL" NUMBER(7,2), 
 "COMM" NUMBER(7,2),  "DEPTNO" NUMBER(2,0), 
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") USING INDEX
PCTFREE 10 
INITRANS 2
 MAXTRANS 255 
COMPUTE STATISTICS  STORAGE(INITIAL 65536 NEXT 1048576 
MINEXTENTS 1 
MAXEXTENTS 2147483645
PCTINCREASE 0 
FREELISTS 1
FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"  ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE) 
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)   TABLESPACE "USERS"                                                                                                                                                                          
                                                                                                                                                                
SQL> 
SQL> spool off;

Similarly, you can get the DDL for Functions, pacakages, stored procedure etc.

select dbms_metadata.get_ddl('PACKAGE','PACKAGE_NAME','SCHEMA_NAME') from dual;

select dbms_metadata.get_ddl('PACKAGE BODY','PACKAGEBODY_NAME','SCHEMA_NAME') from dual;

select dbms_metadata.get_ddl('TRIGGER','TRIGGER_NAME','SCHEMA_NAME') from dual;