Generate DDL for all Tables in a Schema


set pagesize 0
set linesize 1000
set long 500000
set head off
set trimspool on
set feedback off
set echo off
set verify off

column theClob format a400

accept schemaName prompt "Enter Schema Name: "
accept outputFile prompt "Enter Output File: "

spool '&outputFile'

EXEC dbms_metadata.SET_TRANSFORM_PARAM (dbms_metadata.SESSION_TRANSFORM, 'PRETTY', true);

SELECT DBMS_METADATA.GET_DDL('TABLE', dba_tables.table_name, '&schemaName') as theClob from dba_tables where owner = '&schemaName';

spool off