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
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.
