Script schema creation for Oracle tables

GenerateOracleDDL.md

if the table isn't local i.e. database link you'll need to create a local version to sniff

CREATE TABLE foo AS SELECT * FROM SCHEMA.TABLE@DBLINK.WORLD WHERE rownum < 11;

but surely this is enough? actually no, this will be a best guess by oracle.

if you need more control then the next bit of sql will allow you to tailor the schema create script without all the scripted guff that oracle likes to add.

	SELECT DECODE(A.COLUMN_ID,1,'CREATE TABLE '
		|| A.TABLE_NAME
		|| '(\n','')
		|| RPAD(COLUMN_NAME ,50)
		|| DECODE( DATA_TYPE,'VARCHAR2',DATA_TYPE
		||'('
		||DATA_LENGTH
		|| ')', 'NUMBER',DATA_TYPE
		||'('
		||DATA_PRECISION
		|| DECODE(DATA_SCALE,0,')', ','
		|| DATA_SCALE
		|| ')'), DATA_TYPE)
		|| DECODE(NULLABLE,'N','          NOT NULL','')
		|| DECODE( SIGN(A.COLUMN_ID - b.COLUMN_ID), 0, ' );',' ,') AS FOO
FROM ALL_TAB_COLUMNS A,
		(SELECT TABLE_NAME,
			MAX(COLUMN_ID) COLUMN_ID
			FROM ALL_TAB_COLUMNS
			WHERE OWNER = 'OWNER_WHERE_LOCAL_COPY_OF_FOO_RESIDES'
			GROUP BY TABLE_NAME
		)B
WHERE A.TABLE_NAME = B.TABLE_NAME
	AND A.TABLE_NAME  IN ('foo')
ORDER BY A.TABLE_NAME
	,  A.COLUMN_ID;

TODO: Fix char analysis seems to default to just char instead of char(length) #

another alernative might be to dump to file as a clob using dbms_metadata.get_ddl

DECLARE
    data CLOB;
    objType varchar2(30) := 'TABLE';
    objSchema varchar2(30) := 'SCOTT';
    objName varchar2(30) := 'EMP';
    fname varchar2(256) := objType || '_' || objSchema || '_' || objName || '.sql';
BEGIN
    SELECT dbms_metadata.get_ddl(objType,objName,objSchema) into data from dual;
    DBMS_XSLPROCESSOR.CLOB2FILE(data,'DATA_PUMP_DIR',fname);
END;

source: http://dba.stackexchange.com/questions/8392/script-out-oracle-ddl-in-an-automated-fashion