Re: any unix/linux script to generate schema DDL?

From: Rich Jesse <>
Date: Wed, 6 Aug 2008 11:07:33 -0500 (CDT)
Message-ID: <50094.>

Hi Guang,

It depends on exactly what you're looking for. I use Data Pump to take daily DDL snapshots in our development database using a superset of this Korn script fragment:

ThisDate=`date +\%y\%m\%d\%H\%M\%S`
SCHEMA_FILE="daily_schema_ddl_${ORACLE_SID}_${ThisDate}" TS_FILE="daily_ts_ddl_${ORACLE_SID}_${ThisDate}"

# Export of schema DDL.
time expdp / \

        directory=daily_ddl_dir \
        dumpfile=${SCHEMA_FILE}.expdp \
        logfile=${SCHEMA_FILE}.log_${ThisDate} \
        content=metadata_only \
        exclude=statistics,grant \
        schemas=MYSCHEMA1, MYSCHEMA2, MYSCHEMA3 \

# Compress the schema exports.
time gzip $ADMIN/LOGS/${SCHEMA_FILE}.expdp

# Export of tablespace DDL and their associated data files. time expdp / \

        directory=daily_ddl_dir \
        dumpfile=${TS_FILE}.expdp \
        logfile=${TS_FILE}.log_${ThisDate} \
        include=tablespace \

Of course, you MUST modify the above to suit your needs (e.g. the "directory", "exclude" and "schemas" clauses). And be sure to test it out first to make sure it'll do what you're thinking it will. :)

HTH! GL! Rich

> Oracle
> Does anyone know any sites or URL that has unix/linux script(s) to generate
> a schema DDL (including tables, index, views, package code, store procs,
> etc), say using soemthing like DBMS_METADATA.GET_DDL? I can use Oracle
> SQLDeveloper to do it manually, but I would like to have the script called
> by a cron job. BTW, I do not want to use dump file to import the db
> structure, just some unix/linux script to "extract" schema DDL. I googled
> around but could not find one.
> TIA.
> Guang

Received on Wed Aug 06 2008 - 11:07:33 CDT

Original text of this message