Using DBMS_METADATA To Get The DDL For Objects

PURPOSE


The purpose of this document is illustrate the usage of dbms_metadata to generate the DDL for objects.


SCOPE & APPLICATION


The DBMS_METADATA package is a powerful tool for obtaining the complete definition
of a schema object. It enables you to obtain all of the attributes
of an object in one pass. The object is described as DDL that can be used to (re)create it.

The GET_DDL function is used to fetch the DDL for all tables in the current schema,
filtering out nested tables and overflow segments.
The SET_TRANSFORM_PARAM (with the handle value equal to DBMS_METADATA.SESSION_TRANSFORM
meaning "for the current session") is used to
specify that storage clauses are not to be returned in the SQL DDL.

Afterwards, the session-level transform parameters are reset to their defaults.
Once set, transform parameter values remain in effect until specifically reset
to their defaults.


Note: Please note that you would require to run catmeta.sql for the creation of the views related to DBMS_METADATA.
This Script is available under $ORACLE_HOME/rdbms/admin directory.


For E.g if you had created a table

create table idx3_tab (
name varchar2(30),
id number,
addr varchar2(100),
phone varchar2(30)) tablespace users;

And then had wanted to generate the table creation script run the following
query:

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:
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
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"


Another example would be
create type person as object (
name varchar2(20),
age number);
/
create type v0 as varray(5) of person;
/
create type n1 as table of v0;
/
create type n2 as object (n2_c1 n1);
/

create table tab11 (
c1 n2)
nested table c1.n2_c1 store as tab11_c1_n1 (
varray column_value store as lob tab11_c1_v1)
RETURN AS LOCATOR;

select dbms_metadata.get_ddl(TABLE,TAB11) from dual;
would show an output like
CREATE TABLE "SCOTT"."TAB11"
( "C1" "SCOTT"."N2"
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING TABLESPACE "USERS"
NESTED TABLE "C1"."N2_C1" STORE AS "TAB11_C1_N1"
(PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
VARRAY "COLUMN_VALUE" STORE AS LOB "TAB11_C1_V1"
(ENABLE STORAGE IN ROW CHUNK 4096 PCTVERSION 10
CACHE )) RETURN AS LOCATOR


This tool would avoid the work of writing a select statement which would combine
data dictionary views to get the desired output.
.
If we want the definition of all the objects in the database we could get
the definition from the export dump.
.