set long 20000; select dbms_metadata.get_ddl('TABLE',u.table_name) from user_tables u where table_name ='T1'DBMS_METADATA With DBMS_METADATA you can retrieve complete database object definitions (metadata) from the dictionary by specifying: The type of object, for example, tables, indexes, or procedures Optional selection criteria, such as owner or name Optional transformations on the output. By default the output is represented in XML, but callers can specify transformations (into SQL DDL, for example), which are implemented by XSL-T stylesheets stored in the database or externally. DBMS_METADATA provides the following retrieval interfaces: For programmatic use: OPEN, SET_FILTER, SET_COUNT, GET_QUERY, SET_PARSE_ITEM, ADD_TRANSFORM, SET_TRANSFORM_PARAM, FETCH_xxx and CLOSE retrieve multiple objects. For browsing: GET_XML and GET_DDL return metadata for a single object and are used in SQL queries and for browsingFUNCTION get_ddl ( object_type IN VARCHAR2, name N VARCHAR2, schema IN VARCHAR2 DEFAULT NULL, version IN VARCHAR2 DEFAULT 'COMPATIBLE', model IN VARCHAR2 DEFAULT 'ORACLE', transform IN VARCHAR2 DEFAULT 'DDL') RETURN CLOB;object_type The type of object to be retrieved. This parameter takes the same values as the OPEN object_type parameter. In addition the following types may be specified: PACKAGE_SPEC - package specification (without body) PACKAGE_BODY - package body TYPE_SPEC - type specification (without body) TYPE_BODY - type body
name An object name (case-sensitive). If object_type is SYNONYM and name is longer than 30 characters, then name will be treated as a LONGNAME filter. See Table 28-5.
schema A schema name (case sensitive). The default is the current schema if object_type refers to a schema object; otherwise the default is NULL.
version The version of metadata to be extracted. This parameter takes the same values as the OPEN version parameter.
model The object model to use. This parameter takes the same values as the OPEN model parameter.
transform The name of a transformation on the output. This parameter takes the same values as the ADD_TRANSFORM name parameter. For GET_XML this must not be DDL.
select dbms_metadata.get_ddl('TABLE',u.table_name)
from user_tables u
where table_name ='T1'DBMS_METADATA
With DBMS_METADATA you can retrieve complete database object definitions (metadata) from the dictionary by specifying: The type of object, for example, tables, indexes, or procedures Optional selection criteria, such as owner or name Optional transformations on the output. By default the output is represented in XML, but callers can specify transformations (into SQL DDL, for example), which are implemented by XSL-T stylesheets stored in the database or externally. DBMS_METADATA provides the following retrieval interfaces: For programmatic use: OPEN, SET_FILTER, SET_COUNT, GET_QUERY, SET_PARSE_ITEM, ADD_TRANSFORM, SET_TRANSFORM_PARAM, FETCH_xxx and CLOSE retrieve multiple objects. For browsing: GET_XML and GET_DDL return metadata for a single object and are used in SQL queries and for browsingFUNCTION get_ddl (
object_type IN VARCHAR2,
name N VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL,
version IN VARCHAR2 DEFAULT 'COMPATIBLE',
model IN VARCHAR2 DEFAULT 'ORACLE',
transform IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;object_type
The type of object to be retrieved. This parameter takes the same values as the OPEN object_type parameter. In addition the following types may be specified: PACKAGE_SPEC - package specification (without body) PACKAGE_BODY - package body TYPE_SPEC - type specification (without body) TYPE_BODY - type body
name
An object name (case-sensitive). If object_type is SYNONYM and name is longer than 30 characters, then name will be treated as a LONGNAME filter. See Table 28-5.
schema
A schema name (case sensitive). The default is the current schema if object_type refers to a schema object; otherwise the default is NULL.
version
The version of metadata to be extracted. This parameter takes the same values as the OPEN version parameter.
model
The object model to use. This parameter takes the same values as the OPEN model parameter.
transform
The name of a transformation on the output. This parameter takes the same values as the ADD_TRANSFORM name parameter. For GET_XML this must not be DDL.