How to get DDL for an object: TABLE, INDEX, PACKAGE....

DBMS_METADATA is a package that can be used to get DDL for TABLE, INDEXES etc. Below is a quick view of how it works:


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

 The output would be:

CREATE TABLE "SCOTT"."IDX3_TAB"
( "NAME" VARCHAR2(30),
"ID" NUMBER,
"ADDR" VARCHAR2(100),
"PHONE" VARCHAR2(30)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"


To get the create table definition without the storage clause you could do as follows:

SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

The output should be PL/SQL procedure successfully completed.


And then if you run

set long 100000
select dbms_metadata.get_ddl('TABLE','IDX3_TAB') from dual;

would return

CREATE TABLE "SCOTT"."IDX3_TAB"
( "NAME" VARCHAR2(30),
"ID" NUMBER,
"ADDR" VARCHAR2(100),
"PHONE" VARCHAR2(30)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING TABLESPACE "USERS"

But the above package wasn't useful when I tried to get DDL information for a package body based on my experience and DBA_SOURCE from into help for that. Below is an example:
 
SQL> SELECT TEXT FROM DBA_SOURCE WHERE NAME='PKG_DEPT_PROCESS';