How to Get DDL with DBMS_METADATA.GET_DDL

The CREATE statement of a table, index, view, or procedure can be rebuilt from the data dictionary at any time. DBMS_METADATA.GET_DDL returns it as a CLOB, and transform parameters control what it includes, such as storage clauses and statement terminators. GET_DEPENDENT_DDL returns the DDL of objects that belong to another, such as a table's indexes.

Code for This Guide

The main examples are in the examples/pkg-metadata folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

dbms_metadata.get_ddl(object_type, name [, schema]) return clob
dbms_metadata.get_dependent_ddl(object_type, base_object_name [, base_object_schema])
dbms_metadata.set_transform_param(dbms_metadata.session_transform, name, value);

Common transform parameters are SEGMENT_ATTRIBUTES, STORAGE, TABLESPACE, CONSTRAINTS, SQLTERMINATOR, and PRETTY. 'DEFAULT' resets them all.

A Table and Its Indexes

Example:

begin
  -- start from the defaults (SQLcl sets its own for its DDL command), then:
  dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'DEFAULT');
  -- no storage details, and a terminator after each statement
  dbms_metadata.set_transform_param(dbms_metadata.session_transform,
                                    'SEGMENT_ATTRIBUTES', false);
  dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', true);
end;
/
select dbms_metadata.get_ddl('TABLE', 'COUNTRIES') as ddl from dual;

select dbms_metadata.get_dependent_ddl('INDEX', 'TICKETS') as indexes from dual;

Output:

PL/SQL procedure successfully completed.

DDL
___________________________________________________________

  CREATE TABLE "NIMBUS"."COUNTRIES"
   (	"COUNTRY_CODE" CHAR(2),
	"COUNTRY_NAME" VARCHAR2(60) NOT NULL ENABLE,
	"REGION" VARCHAR2(30) NOT NULL ENABLE,
	"CURRENCY_CODE" CHAR(3) NOT NULL ENABLE,
	 CONSTRAINT "COUNTRIES_PK" PRIMARY KEY ("COUNTRY_CODE")
  USING INDEX  ENABLE
   ) ;

INDEXES
_____________________________________________________________________________________

  CREATE UNIQUE INDEX "NIMBUS"."TICKETS_PK" ON "NIMBUS"."TICKETS" ("TICKET_ID")
  ;
  CREATE INDEX "NIMBUS"."TICKETS_BOOKING_IX" ON "NIMBUS"."TICKETS" ("BOOKING_ID")
  ;
  CREATE INDEX "NIMBUS"."TICKETS_FLIGHT_IX" ON "NIMBUS"."TICKETS" ("FLIGHT_ID")
  ;

With SEGMENT_ATTRIBUTES off, the CREATE TABLE has no storage or tablespace clauses, and SQLTERMINATOR adds the semicolons. GET_DEPENDENT_DDL returns all three indexes of TICKETS in one CLOB.

Foreign Keys as ALTER Statements

Example:

begin
  dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'DEFAULT');
  dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', true);
end;
/
-- the foreign keys of TICKETS, as ALTER TABLE statements
select dbms_metadata.get_dependent_ddl('REF_CONSTRAINT', 'TICKETS') as foreign_keys
from   dual;

Output:

PL/SQL procedure successfully completed.

FOREIGN_KEYS
__________________________________________________________________________________________________

  ALTER TABLE "NIMBUS"."TICKETS" ADD CONSTRAINT "TICKETS_BOOKING_FK" FOREIGN KEY ("BOOKING_ID")
	  REFERENCES "NIMBUS"."BOOKINGS" ("BOOKING_ID") ENABLE;
  ALTER TABLE "NIMBUS"."TICKETS" ADD CONSTRAINT "TICKETS_FLIGHT_FK" FOREIGN KEY ("FLIGHT_ID")
	  REFERENCES "NIMBUS"."FLIGHTS" ("FLIGHT_ID") ENABLE;

REF_CONSTRAINT returns each foreign key of TICKETS as an ALTER TABLE statement, which is how a script adds them after all the tables exist.

Things to Know

• Object types and names are in uppercase, as stored in the dictionary.
• Session transform parameters stay set until changed or reset with 'DEFAULT'; tools such as SQLcl set their own.
• Reading DDL of other schemas needs SELECT_CATALOG_ROLE or the owner's privileges.

Related Guides

• How to Get Object Metadata with DBMS_DEVELOPER.GET_METADATA
• Oracle Create Table Examples

Conclusion

DBMS_METADATA.GET_DDL rebuilds the CREATE statement of any object, and GET_DEPENDENT_DDL adds its indexes, constraints, and grants. Set transform parameters to leave out storage details and add terminators, so the result runs as a script.