Search notes:

DBMS_METADATA.GET_DDL

DBMS_METADATA.GET_DDL returns the definition of a single object as DDL statement.

This function is meant for casual browsing. For more detailed object access, the open, fetch_*, close interface should be used.

FUNCTION get_ddl (
              object_type     IN  VARCHAR2,
              name            IN  VARCHAR2,
              schema          IN  VARCHAR2 DEFAULT  NULL,
              version         IN  VARCHAR2 DEFAULT 'COMPATIBLE',
              model           IN  VARCHAR2 DEFAULT 'ORACLE',
              transform       IN  VARCHAR2 DEFAULT 'DDL')
      RETURN CLOB;

begin
   dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'PRETTY'            , true );
   dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'CONSTRAINTS'       , true );
   dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'STORAGE'           , false);
   dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', false);
end;
/

select
   dbms_metadata.get_ddl('TABLE', 'TQ84_TAB', user)
from
   dual;

When using dbms_metadata.get_ddl in SQL*Plus, the following settings should be adjusted (see also Displying CLOB values in SQL*Plus).

set pagesize             0
set long          10000000
set longchunksize 10000000
set linesize         32767

DBMS_METADATA.GET_DDL

See also