This script will generate the DDL to re-create a specific table. The script accepts two parameters:
1. Schema name
2. Table name

def S='&1'
def T='&2'
-- def I='&3'
set lines 2000 pages 0 ver off echo off head off feed off
set newpage none
set trimspool on
set long 5000000
col output for a1000 word_wrapped
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',false);

spool &T..sql
select dbms_metadata.get_ddl('TABLE','&T','&S') output from dual;

spool off;

Created by Charles Kim, Oracle ACE Director

Leave a Reply

Your email address will not be published. Required fields are marked *