How to get the DDL for a table in Oracle

Regenerate the CREATE TABLE statement (including constraints, indexes, and storage) via DBMS_METADATA. Give a schema and either a specific table name or ALL to dump every table in the schema. PRETTY + SQLTERMINATOR make the output copy-paste ready.

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

BEGIN
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY',        TRUE);
  -- Uncomment to skip physical storage clauses:
  -- DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE);
  -- DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE',            FALSE);
END;
/

ACCEPT p_schema CHAR PROMPT 'Schema: '
ACCEPT p_table  CHAR PROMPT 'Table name (or ALL): '

SELECT DBMS_METADATA.GET_DDL('TABLE', table_name, owner) AS ddl
FROM   all_tables
WHERE  owner      = UPPER('&&p_schema')
AND    table_name = DECODE(UPPER('&&p_table'), 'ALL', table_name, UPPER('&&p_table'));

SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON