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

Hi Guang,

It depends on exactly what you're looking for. I use Data Pump to take daily DDL snapshots in our 10.1.0.5.0 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 \
        job_name=daily_schema_ddl

# 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 \
        full=y

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 10.2.0.1.0
>
> 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

--
http://www.freelists.org/webpage/oracle-l