dbms_metadata
exposes the Metadata API which allows to extract definitions of objects as DDL statements or XML documents. dbms_metadata
is also used by Data Pump. 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;
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
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 and procedures return metadata for objects that meet the criteria set forth with open
set_filter
set_count
add_transform
fetch*
call retrieves objects of one object type only. select distinct procedure_name from all_procedures where object_name = 'DBMS_METADATA' order by procedure_name
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. |
dbms_metadata.open
like so: begin dbms_metadata.open('TABLE_EXPORT' ); -- dbms_metadata.open('SCHEMA_EXPORT' ); -- dbms_metadata.open('DATABASE_EXPORT');
het_type
of dba_export_objects
. dbms_metadata
disables session migration and connection pooling. SELECT_CATALOG_ROLE
privilege is required. dba_source
shows the source code of PL/SQL objects. dbms_metadata
with dbms_ddl
.