Scripts   Home

Export Oracle Data Definition Language (DDL) Scripts and Extract Oracle DDL

Oracle is very anal retentive about getting usable and complete data definition language (DDL) out of its database servers before release 9i and still provides very limited schema utilities. This was written to extract the data definition language (DDL) for database development projects and configuration management and the end result is a runable DDL script to create a database or a user schema.

How to do it:
Create an Oracle exp (export) file without the data.
Use the Oracle imp (import) utility to get the DDL (the show option) from an Oracle exp (export) file to make a text file.
Then use the shell-awk filter below on the text file to create the DDL script can be used by sqlplus or some SQL interpreter.

#!/bin/ksh
# Make DDL usable for Oracle svrmgrl or sqlplus from the Oracle 
# export/import utilities.
# More stuff at http://www.sofbot.com and http://www.tc.umn.edu/~hause011/

# How to do it: Use Oracle imp (import) utility to get the DDL from  
# an Oracle exp (export) file.  Then filter textfile with this awk. 
# The resulting DDL can be used by sqlplus or used to convert 
# to a different database product.
 
# Example:
# exp name/password full=y rows=n ...
# imp name/password show=y full=y file=xxxnamexx.dmp > textfile
# ugly_DDL.ksh textfile > bunch_of_SQL.sql

# Note: to remove storage declarations uncomment the line:
# '# remove_tablespace' near the bottom of this script.

# NOTE: Configure which awk to use right after "# Main" below. 

# Known Bugs: 
# DATE: 1/3/1999 
# PROBLEM: Line to long for awk to process
# FIX: Use gawk instead of awk or nawk. Or convert to perl.

# DATE: 1/4/1999 
# PROBLEM: arnold.schommer@io-computer.de has shown that for 
#     PL/SQL code blocks, especially Package declarations,
#     if there are comments starting with -- , in some - not all - cases, 
#     the script joins the following row.
# FIX: None at this time.  A work-around would be to edit the output of the
#     PL/SQL code before running it in an interpreter.
#


# Find lines with beginning 'space doublequote' and length < 78.
# Remove beginning 'space doublequote' and end doublequote, add space at end
# as they end at whole tokens.
# Find lines with beginning 'space doublequote' and length = 78.
# Remove beginning 'space doublequote' and end doublequote
# Break at the beginning of DDL statements. 
get_statements () {
    $AWK '
      /^ "/ { if (length($0)==78) {gsub("\"$","",$0);} 
                 else  {gsub("\"$"," ",$0);};  
              gsub("^ \"","",$0); 
              sub("^CREATE ","\nCREATE ",$0);
              sub("^ALTER ","\nALTER ",$0);
              sub("^COMMENT ","\nCOMMENT ",$0);
#              sub("--","\n--",$0);
#              sub("PROCEDURE ","\nPROCEDURE ",$0);
#              sub("FUNCTION ","\nFUNCTION ",$0);
              sub("^GRANT ","\nGRANT ",$0);
              print }
    ' 
}

# Grab whole blank line delimited records, put a semicolon at the
# end of a record then join the broken tokens by removing \n in the records.
join_tokens () {
    $AWK '
          BEGIN { RS="" } 
          { gsub("\n","",$0); }
          { print $0";\n" }
    ' 
}

# Remove tablespace and extent declarations.
remove_tablespace () {
    $AWK '
         { sub(" PCTFREE.*;",";",$0); }
         { print }
    ' 
}

# Fix line lengths; sqlplus and vi have problems with long lines.
semicolon_break () {
    $AWK '
         /;.*/ && /CREATE TRIGGER/ { gsub(";",";\n",$0); }
         /;.*/ && /CREATE PACKAGE/ { gsub(";",";\n",$0); }
         /;.*/ && /CREATE FUNCTION/ { gsub(";",";\n",$0); }
         { print }
    ' 
}

# Fix line lengths; sqlplus and vi have problems with long lines.
comma_break () {
    $AWK '
         length($0)> 1024  { gsub(",",",\n",$0); }
         { print }
    ' 
}

# Usage message.
usage () {
    echo;
    echo 'Usage: '$0 ''
    echo;
    exit 
}


###################################
# Main
#
AWK=/usr/bin/awk; export AWK

test $# -eq 1 || usage

cat $1|
get_statements |
join_tokens |
#remove_tablespace |
semicolon_break |
comma_break

#############################################