Search notes:

Oracle: DBMS_METADATA

dbms_metadata exposes the Metadata API which allows to extract definitions of objects as DDL statements or XML documents.
The extracted definitions can then be used to recreate these objects.
dbms_metadata is also used by Data Pump.

Simple example

create table table_heap (
   a number,
   b varchar2(10)
);

create global temporary table table_ggt (
   a number,
   b varchar2(10)
);

select dbms_metadata.get_ddl('TABLE', 'TABLE_HEAP') from dual;
select dbms_metadata.get_ddl('TABLE', 'TABLE_GGT' ) from dual;
An example that extracts a table's primary key, foreign keys and indexes separately is here.

Using in SQL*Plus with spool

dbms_metadata.get_ddl might be used in SQL*Plus and the spool command to extract the definition of an object into a file that then can be used to re-create the table:
set long 10000
spool /tmp/table-def.sql
select dbms_metadata.get_ddl('TABLE','JUST_A_TABLE') from dual;
spool off

Procedures and functions

The API differentiates between functions intended to be used
The former are marked with prog, the latter with browse in the following table.
ADD_TRANSFORM Specifies a transformation to be applied to the XML documents when using FETCH_*, CONVERT or PUT functions/procedures. See also SET_TRANSFORM_PARAM prog
CHECK_CONSTRAINT
CHECK_MATCH_TEMPLATE
CHECK_MATCH_TEMPLATE_LOB
CHECK_MATCH_TEMPLATE_PAR
CHECK_TYPE
CLOSE Cleans up the fetch context which was established by calling OPEN prog
CONVERT Converts an XML document to a DDL statement
CONVERT_TO_CANONICAL
FETCH_CLOB prog
FETCH_DDL Returns the DDL statements an object in a sys.ku$_ddls nested table. prog
FETCH_DDL_TEXT prog
FETCH_OBJNUMS A table(…) function that returns object numbers which is used to speed up heterogeneous fetching. prog
FETCH_OBJNUMS_NAMES prog
FETCH_SORTED_OBJNUMS prog
FETCH_VAT_OBJNUMS prog
FETCH_XML Return metadata for an objects as XMLType prog
FETCH_XML_CLOB prog
FREE_CONTEXT_ENTRY
GET_ACTION_INSTANCE
GET_ACTION_SCHEMA
GET_ACTION_SYS
GET_CANONICAL_VSN
GET_CHECK_CONSTRAINT_NAME
GET_DDL GET_DDL returns the definition of a single object and is meant for casual browsing. browse
GET_DEPENDENT_DDL browse
GET_DEPENDENT_SXML
GET_DEPENDENT_XML browse
GET_DOMIDX_METADATA Used in conjunction with domain indexes.
GET_DPSTRM_MD
GET_EDITION
GET_EDITION_ID
GET_FK_CONSTRAINT_NAME
GET_GRANTED_DDL browse
GET_GRANTED_XML browse
GET_HASHCODE
GET_INDEX_INTCOL
GET_INDPART_TS
GET_JAVA_METADATA
GET_PARTN
GET_PLSQL_OPTIMIZE_LEVEL
GET_PLUGTS_BLK
GET_PREPOST_TABLE_ACT
GET_PROCOBJ
GET_PROCOBJ_GRANT
GET_QUERY Get the text of the SQL statement, used for debugging. (See set_debug procedure) prog
GET_STAT_COLNAME
GET_STAT_INDNAME
GET_SXML browse
GET_SXML_DDL
GET_SYSPRIVS
GET_VERSION
GET_XML browse
IN_TSNUM
IN_TSNUM_2
IS_ACTIVE_REGISTRATION
IS_ATTR_VALID_ON_10
IS_XDB_TRANS
NETWORK_CALLOUTS
NETWORK_FETCH_CLOB
NETWORK_FETCH_ERRORS
NETWORK_FETCH_PARSE
NETWORK_OPEN
NET_SET_DEBUG
OKTOEXP_2NDARY_TABLE
OPEN Establish a fetch context (object parameters) to be used with FETCH_* procedures. In essence, OPEN specifies the object types whose meta data is to be retrieved. See also SET_FILTER and CLOSE. prog
OPENW Specifies the object-type whose metadata is to be submitted. The W possibly stands for write.
OPEN_GET_FK_CONSTRAINT_NAME
PARSE_CONDITION
PARSE_DEFAULT
PARSE_QUERY
PATCH_TYPEID
PUT
SET_COUNT Sets the maximum number of objects to be retrieved with single calls to FETCH_… prog
SET_DEBUG Sets (internal) debug flag
SET_FILTER Set the criteria («restrictions») which specify which objects should be returned. See also OPEN. prog
SET_FK_CONSTRAINT_COL_PAIR
SET_PARAMETER Sets parameters for FETCH_* operations.
SET_PARSE_ITEM Enables output parsing and specifies object attributes. Call SET_PARSE_ITEM so that FETCH_DDL and FETCH_XML_CLOB return attributes of the object's metadata or the DDL statement in a sys.ku$_parsed_items nested table. prog
SET_REMAP_PARAM prog
SET_TRANSFORM_PARAM See also ADD_TRANSFORM prog
SET_XMLFORMAT Specifies the formatting attributes when creating XML output
TRANSFORM_STRM

FETCH* Functions/procedures

The fetch functions and procedures return metadata for objects that meet the criteria set forth with
  • open
  • set_filter
  • set_count
  • add_transform
  • etc.
When fetching heterogeneous object types, a single fetch* call retrieves objects of one object type only.
The names of the above list where created with
select distinct
   procedure_name
from
   all_procedures
where
   object_name = 'DBMS_METADATA'
order by
   procedure_name

Views

Some views that somehow seem to be related to DBMS_METADATA can be queried from dba_views:
select *
from
   dba_views
where
   owner     =    'SYS'             and
   view_name like 'DBMS_METADATA%'
-- order by
--    view_name
;
dbms_metadata_tparams_base Base view for transform parameters and parse items. Joins sys.metaview$, sys.metaxsl$, sys.metaxslparam$ and sys.metaxlsparamdesc$.
dbms_metadata_all_tparams Selects from dbms_metadata_tparams_base to return all transform parameters.
dbms_metadata_all_parse_items Selects from dbms_metadata_all_parse_items where transform = 'PARSE'.
dbms_metadata_all_transforms Joins sys.metaview$ with sys.metaxsl$ on xmltag
dbms_metadata_transforms Selects from dbms_metadata_all_transforms but excludes the transforms 'EDITIONING_VIEW, MODIFYSTREAMSXML and PARSE.
dbms_metadata_parse_items Selects from dbms_metadata_all_parse_items but excludes internal params (t.internal = 'N') and thus becomes the public view for parse items.
dbms_metadata_transform_params The public view for transform params.

Heterogeneous object types

Oracle's documentation refers to objects that logically belong together as heterogeneous object types.
These heterogeneous object types are:
A heterogeneous object type can be specified in dbms_metadata.open like so:
begin
   dbms_metadata.open('TABLE_EXPORT'   );
-- dbms_metadata.open('SCHEMA_EXPORT'  );
-- dbms_metadata.open('DATABASE_EXPORT');
See also the column het_type of dba_export_objects.

Misc

When used in an Oracle Shared Server (OSS) environment, dbms_metadata disables session migration and connection pooling.
When using get* functions SQL*Plus, pagesize should be 0 and long to a value that is large enough to print contiguous text.

See also

In order to extract definitions from another schema, the SELECT_CATALOG_ROLE privilege is required.
Filters
Oracle DBMS PL/SQL packages
dba_source shows the source code of PL/SQL objects.
dba_export_objects
Don't confuse dbms_metadata with dbms_ddl.

Links

mddemo.sql (supposedly located under $ORACLE_HOME/rdbms/demo) demonstrates the dbms_metadata API.

Index