How to generate entire DDL of an Oracle schema (scriptable)?

You can spool the schema out to a file via SQL*Plus and dbms_metadata package. Then replace the schema name with another one via sed. This works for Oracle 10 and higher.

sqlplus<<EOFset long 100000set head offset echo offset pagesize 0set verify offset feedback offspool schema.outselect dbms_metadata.get_ddl(object_type, object_name, owner)from(    --Convert DBA_OBJECTS.OBJECT_TYPE to DBMS_METADATA object type:    select        owner,        --Java object names may need to be converted with DBMS_JAVA.LONGNAME.        --That code is not included since many database don't have Java installed.        object_name,        decode(object_type,            'DATABASE LINK',      'DB_LINK',            'JOB',                'PROCOBJ',            'RULE SET',           'PROCOBJ',            'RULE',               'PROCOBJ',            'EVALUATION CONTEXT', 'PROCOBJ',            'CREDENTIAL',         'PROCOBJ',            'CHAIN',              'PROCOBJ',            'PROGRAM',            'PROCOBJ',            'PACKAGE',            'PACKAGE_SPEC',            'PACKAGE BODY',       'PACKAGE_BODY',            'TYPE',               'TYPE_SPEC',            'TYPE BODY',          'TYPE_BODY',            'MATERIALIZED VIEW',  'MATERIALIZED_VIEW',            'QUEUE',              'AQ_QUEUE',            'JAVA CLASS',         'JAVA_CLASS',            'JAVA TYPE',          'JAVA_TYPE',            'JAVA SOURCE',        'JAVA_SOURCE',            'JAVA RESOURCE',      'JAVA_RESOURCE',            'XML SCHEMA',         'XMLSCHEMA',            object_type        ) object_type    from dba_objects     where owner in ('OWNER1')        --These objects are included with other object types.        and object_type not in ('INDEX PARTITION','INDEX SUBPARTITION',           'LOB','LOB PARTITION','TABLE PARTITION','TABLE SUBPARTITION')        --Ignore system-generated types that support collection processing.        and not (object_type = 'TYPE' and object_name like 'SYS_PLSQL_%')        --Exclude nested tables, their DDL is part of their parent table.        and (owner, object_name) not in (select owner, table_name from dba_nested_tables)        --Exclude overflow segments, their DDL is part of their parent table.        and (owner, object_name) not in (select owner, table_name from dba_tables where iot_type = 'IOT_OVERFLOW'))order by owner, object_type, object_name;spool offquitEOFcat schema.out|sed 's/OWNER1/MYOWNER/g'>schema.out.change.sql

Put everything in a script and run it via cron (scheduler). Exporting objects can be tricky when advanced features are used. Don't be surprised if you need to add some more exceptions to the above code.

If you want to individually generate ddl for each object,

Queries are:

--GENERATE DDL FOR ALL USER OBJECTS

--1. FOR ALL TABLES

SELECT DBMS_METADATA.GET_DDL('TABLE', TABLE_NAME) FROM USER_TABLES;

--2. FOR ALL INDEXES

SELECT DBMS_METADATA.GET_DDL('INDEX', INDEX_NAME) FROM USER_INDEXES WHERE INDEX_TYPE ='NORMAL';

--3. FOR ALL VIEWS

SELECT DBMS_METADATA.GET_DDL('VIEW', VIEW_NAME) FROM USER_VIEWS;

OR

SELECT TEXT FROM USER_VIEWS

--4. FOR ALL MATERILIZED VIEWS

SELECT QUERY FROM USER_MVIEWS

--5. FOR ALL FUNCTION

SELECT DBMS_METADATA.GET_DDL('FUNCTION', OBJECT_NAME) FROM USER_PROCEDURES WHERE OBJECT_TYPE = 'FUNCTION'

===============================================================================================

GET_DDL Function doesnt support for some object_type like LOB,MATERIALIZED VIEW, TABLE PARTITION

SO, Consolidated query for generating DDL will be:

SELECT OBJECT_TYPE, OBJECT_NAME,DBMS_METADATA.GET_DDL(OBJECT_TYPE, OBJECT_NAME, OWNER)  FROM ALL_OBJECTS   WHERE (OWNER = 'XYZ') AND OBJECT_TYPE NOT IN('LOB','MATERIALIZED VIEW', 'TABLE PARTITION') ORDER BY OBJECT_TYPE, OBJECT_NAME;

The get_ddl procedure for a PACKAGE will return both spec AND body, so it will be better to change the query on the all_objects so the package bodies are not returned on the select.

So far I changed the query to this:

SELECT DBMS_METADATA.GET_DDL(REPLACE(object_type, ' ', '_'), object_name, owner)FROM all_OBJECTSWHERE (OWNER = 'OWNER1')and object_type not like '%PARTITION'and object_type not like '%BODY'order by object_type, object_name;

Although other changes might be needed depending on the object types you are getting...