For posterity:

set echo off set heading off set feedback off set long 1999999999 set linesize 32767 set longchunksize 10000 set serveroutput on size unlimited format word_wrapped set pagesize 0 set verify off set trimspool on col owner format a30 col segment_name format a30 col segment_type format a30 col tablespace_name format a30 prompt This script extracts DDL for the given schema prompt Usage: "md.sql " or "md.sql .", prompt e.g. "md.sql scott or md.sql scott.departments" prompt prompt Parameters: 1) schema_name.[object_name] define sch_obj_name = &1 prompt schema_name = &sch_obj_name prompt prompt Output: a sql script named as "..sql", e.g. "scott.dev1.sql" prompt variable v_schema_name varchar2(60) variable v_object_name varchar2(60) exec :v_schema_name := upper('&&sch_obj_name') declare i int; begin i := instr(:v_schema_name, '.'); if i > 1 then :v_object_name := substr(:v_schema_name, i + 1); :v_schema_name := substr(:v_schema_name, 1, i - 1); end if; end; / prompt Schema / object name: prompt print v_schema_name print v_object_name rem Switch end of line between Windows (#13#10) and Unix (#13). rem define eol = chr(13) define eol = chr(13)||chr(10) prompt Extracting: &&sch_obj_name@&_CONNECT_IDENTIFIER into &&sch_obj_name..&_CONNECT_IDENTIFIER..sql spool &&sch_obj_name..&_CONNECT_IDENTIFIER..sql rem --------------------------------------------------; rem objects ddl rem --------------------------------------------------; begin -- set metadata dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',true); dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true); dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',false); dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',false); dbms_metadata.set_transform_param(dbms_metadata.session_transform,'TABLESPACE',true); dbms_metadata.set_transform_param(dbms_metadata.session_transform,'BODY',true); dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SPECIFICATION',true); dbms_metadata.set_transform_param(dbms_metadata.session_transform,'BODY',false); dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONSTRAINTS',false); dbms_metadata.set_transform_param(dbms_metadata.session_transform,'REF_CONSTRAINTS',false); dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONSTRAINTS_AS_ALTER',false); end; / -- select objects select '/* owner=' || oo.owner || ',object_name='|| case when oo.object_type = 'INDEX' then ii.table_name else oo.object_name end ||',object_type='||oo.object_type||',status='||oo.status|| ' */' || &&eol || regexp_replace(regexp_replace(dbms_metadata.get_ddl (replace(oo.object_type, ' ', '_'), oo.object_name, oo.owner), '\s+;', ';', 1, 0), ' /$', chr(10) || '/' || chr(10), 1, 1, 'm') from dba_objects oo left outer join dba_indexes ii on ii.index_name = oo.object_name and ii.owner = oo.owner where oo.owner = :v_schema_name and object_name = nvl(:v_object_name, object_name) and oo.object_type not in ('RULE', 'LOB', 'QUEUE', 'EVALUATION CONTEXT', 'TABLE PARTITION', 'RULE SET', 'JAVA CLASS', 'TABLE SUBPARTITION', 'INDEX PARTITION', 'INDEX SUBPARTITION', 'PROGRAM', 'SCHEDULE', 'JOB', 'DATABASE LINK') and oo.object_name not like 'SYS_IOT_OVER%' and oo.object_name not like 'BIN$%' and oo.subobject_name is null order by oo.object_name, oo.object_type; -- constraints, ordered select '/* owner=' || owner || ',object_name=' || table_name || ',object_type=CONSTRAINT,status=' || status || ' */ ' || &&eol || dbms_metadata.get_ddl(case constraint_type when 'R' then 'REF_CONSTRAINT' else 'CONSTRAINT' end , constraint_name, owner) from dba_constraints where owner = :v_schema_name and table_name = nvl(:v_object_name, table_name) and constraint_type in ('C', 'P', 'U', 'R') and constraint_name not like 'BIN$%' order by owner, table_name, constraint_name; rem --------------------------------------------------; rem user grants rem --------------------------------------------------; select '/* owner=' || owner || ',object_name=' || table_name || ',object_type=USER_GRANT,status=VALID */ ' || &&eol || 'grant ' || privilege || ' on ' || owner || '.' || table_name || ' to ' || grantee || case grantable when 'YES' then ' with grant option ' else null end || ';' from ( select unique owner, table_name, privilege, grantee, grantable from dba_tab_privs where grantor = :v_schema_name and table_name = nvl(:v_object_name, table_name) order by 1,2,3,4,5); rem --------------------------------------------------; rem role grants rem --------------------------------------------------; select '/* owner=SYSTEM,object_name=ROLES,object_type=ROLE_GRANT,status=VALID */ ' || &&eol || 'grant ' || granted_role || ' to ' || grantee || case admin_option when 'YES' then ' with admin option' else null end || ';' from ( select grantee, granted_role, admin_option from dba_role_privs where grantee = :v_schema_name and grantee = nvl(:v_object_name, grantee) order by 1,2,3); rem --------------------------------------------------; rem comments rem --------------------------------------------------; select '/* owner=' || owner || ',object_name=' || table_name || ',object_type=COMMENT,status=VALID */ ' || &&eol || 'comment on table "' || owner || '".' || table_name || ' is ''' || comments || ''';' from dba_tab_comments where owner = :v_schema_name and table_name = nvl(:v_object_name, table_name) order by owner, table_name; select '/* owner=' || owner || ',object_name=' || mview_name || ',object_type=COMMENT,status=VALID */ ' || &&eol || 'comment on materialized view "' || owner || '".' || mview_name || ' is ''' || comments || ''';' from dba_mview_comments where owner = :v_schema_name and mview_name = nvl(:v_object_name, mview_name) order by owner, mview_name; select '/* owner=' || owner || ',object_name=' || table_name || ',object_type=COMMENT,status=VALID */ ' || &&eol || 'comment on column "' || owner || '".' || table_name || '.' || column_name || ' is ''' || comments || ''';' from dba_col_comments where owner = :v_schema_name and table_name = nvl(:v_object_name, table_name) order by owner, table_name, column_name; rem --------------------------------------------------; rem tablespaces rem --------------------------------------------------; select '/* owner=' || :v_schema_name || ',object_name=SEGMENTS,object_type=SEGMENT,status=VALID */' from dual; select distinct owner, segment_name, segment_type, tablespace_name from dba_segments where owner = :v_schema_name and segment_name = nvl(:v_object_name, segment_name) order by owner, segment_name, tablespace_name; rem --------------------------------------------------; rem extract end rem --------------------------------------------------; spool off prompt Extracted: &&sch_obj_name@&_CONNECT_IDENTIFIER into &&sch_obj_name..&_CONNECT_IDENTIFIER..sql select max('main_object_type=' || replace(object_type, ' ', '_')) from dba_objects where owner = :v_schema_name and object_name = nvl(:v_object_name, object_name); quit