Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Unix scripts to extract DDL from imp show=y
There is one called ugly_DDL that works pretty good. I looks like the below.
x38u:oracle>cat ugly_DDL.ksh
#!/bin/ksh
# Make DDL usable for Oracle svrmgrl or sqlplus from the Oracle
# export/import utilities.
# GPL Copyleft.
# More stuff at 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_at_io-computer.de has shown that for PL/SQL
code blocks
# 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("^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 '<text file name from export/import>'
echo;
exit
}
###################################
# Main
#
AWK=/usr/local/bin/gawk; export AWK
#AWK=/usr/bin/awk; export AWK
test $# -eq 1 || usage
cat $1|
get_statements |
join_tokens |
#remove_tablespace |
semicolon_break |
comma_break
Received on Tue May 22 2001 - 06:45:30 CDT