![dbschema no more spool space dbschema no more spool space](https://i.etsystatic.com/5488326/r/il/6dc1a1/610552826/il_794xN.610552826_io8t.jpg)
To generate the DDL script for an entire SCHEMA i.e. WHERE (OWNER = 'XYZ') AND OBJECT_TYPE NOT IN('LOB','MATERIALIZED VIEW', 'TABLE PARTITION') ORDER BY OBJECT_TYPE, OBJECT_NAME SO, Consolidated query for generating DDL will be: SELECT OBJECT_TYPE, OBJECT_NAME,DBMS_METADATA.GET_DDL(OBJECT_TYPE, OBJECT_NAME, OWNER) GET_DDL Function doesnt support for some object_type like LOB,MATERIALIZED VIEW, TABLE PARTITION FOR ALL FUNCTION SELECT DBMS_METADATA.GET_DDL('FUNCTION', OBJECT_NAME) FROM USER_PROCEDURES WHERE OBJECT_TYPE = 'FUNCTION' FOR ALL MATERILIZED VIEWS SELECT QUERY FROM USER_MVIEWS FOR ALL VIEWS SELECT DBMS_METADATA.GET_DDL('VIEW', VIEW_NAME) FROM USER_VIEWS FOR ALL INDEXES SELECT DBMS_METADATA.GET_DDL('INDEX', INDEX_NAME) FROM USER_INDEXES WHERE INDEX_TYPE ='NORMAL' FOR ALL TABLES SELECT DBMS_METADATA.GET_DDL('TABLE', TABLE_NAME) FROM USER_TABLES If you want to individually generate ddl for each object, Don't be surprised if you need to add some more exceptions to the above code. Exporting objects can be tricky when advanced features are used.
![dbschema no more spool space dbschema no more spool space](https://www.funix.org/fr/linux/images/mail/architecture-mail.jpg)
Put everything in a script and run it via cron (scheduler). Then replace the schema name with another one via sed. You can spool the schema out to a file via SQL*Plus and dbms_metadata package.