Re: Creating DDL from EXPORT

From: Tony Jambu <aaj_at_phantom.telecom.com.au>
Date: 1995/08/23
Message-ID: <41f4go$l6i_at_newsserver.trl.OZ.AU>

In article <40vfu4$bin_at_newsbf02.news.aol.com>, shamooli_at_aol.com (Shamooli) writes:

> How can I create a script of defininition of all the tables (including
> storage parameters, constraints) in a schema using EXP.
> 
> We are using Oracle 7 under OS/2 2.1.
> 
> Thanks for any help.

Have a go at the following utility written by David Bath who is the maintainer of the FAQ. There is another versio out there that is very similar but uses awk and sed.

ta
tony

#!/bin/sh
#
# NAME
# impl2sql7 - _at_(#)Converts Oracle import (no rows) list to SQL script
# SYNOPSIS
# sh: imp system/pw rows=n file=expdat.dmp show=y full=y 2>&1 | \
# impl2sql7 > implist.sql
# csh: imp system/pw rows=n show=y full=y file=expdat.dmp |& \
# impl2sql7 > implist.sql
# DESCRIPTION
# First of all, create a log file from the output of Oracle
# 6.0.30 import utility. Use that as the imput for this script
# which will write a suitable SQL*Plus script to stdout.
#
# This utility is particularly useful when the export file refers
# to "foreign" tablespaces and may have ugly physical storage
# parameters that may be too big for your system. To get the data
# into a smaller system, first run Oracle import utility with
# the "rows=n show=y" options and trap the output to a working
# file. Run this file through impl2sql7 to produce the sqlplus
# script and edit the result to suit your tablespaces and
# physical storage desires.
#
# When this has been done, and the sql script runs OK through
# the database, you can then do the import of the actual rows,
# preferably with "ignore=y commit=y" options set. The data
# should now be in your database with your preferred storage
# parameters.
# NOTES AND WARNINGS
# The first part of the script recognises special strings that
# need to be filtered out or the form the start of a SQL statement.
# The lines on input are then broken up at known "safe" points.
# The second part of the script joins lines where the statement
# was NOT broken at a safe point and does a bit of prettying up.
#
# One thing I have implemented concerned changes to users within the
# dump file. This was a bit of a pain, so I put in a CONNECT
# statement preceded by a REM --- CHANGE USER --- line in the message.
# This should be edited and the user's password put in. Otherwise,
# cut up the file by hand and get the user to run them separately.
#
# Constraints have not yet been tested.
#
# Comments with brackets in them will be uglified.
#
# No manual page yet - still alpha.
#
# Versions of Oracle import other than that distributed with
# v6.0.30.3 (UNIX) have NOT been tested. This refers to the
# executable, NOT the export file itself.
# AUTHOR
# David T. Bath 1991
#MODIFIED:
# 1994:TJambu:Simplified the KEY word searches and included Oracle7 stuff
#
# -e

'/^.............................................................................
...$/!{' -e 's/"$/;/' -e '}' 

sed -e '/^[A-Za-z]/d' \

    -e 's/^\. importing user \([^ ][^ ]*\) *$/REM --- CHANGE USER ---\ CONNECT \1;/' \

    -e '/\. \. skipping/d' \
    -e '/^$/d' \
    -e
'/^.............................................................................

..$/!{' -e 's/"$/;/' -e '}' \

    -e 's/^ "\(GRANT \)/;\
\1/' \

    -e 's/^ "\(CREATE \)/;\
\1/' \

    -e 's/^ "\(ALTER \)/;\
\1/' \

    -e 's/^ "\(ANALYZE \)/;\
\1/' \

    -e 's/^ "\(DROP \)/;\
\1/' \

    -e 's/^ "\(REVOKE \)/;\
\1/' \

    -e 's/^ "\(COMMENT \)/;\
\1/' \

    -e 's/ (/ \
(/g' \

    -e 's/, \([^0-9]\)/, \
\1/g' \

    -e 's/))/)\
)/g' \

    -e 's/ )/ \
)/g' | \

sed -e '/"$/{' -e 'N' -e 's/"\n "//' -e 's/"\n;/;/' -e '}' \
    -e '/"$/{' -e 'N' -e 's/"\n "//' -e 's/"\n;/;/' -e '}' \
    -e '/"$/{' -e 'N' -e 's/"\n "//' -e 's/"\n;/;/' -e '}' \
    -e 's/, "/, \

"/g' \

    -e 's/))/)\
)/g' \

    -e 's/) PCTFREE/) \
PCTFREE/g' \

    -e 's/ STORAGE(/ \
STORAGE \
(/g' \

    -e 's/) TABLESPACE/) \
TABLESPACE/g' \

    -e 's/^(\(.\)/(\
\1/'

-- 
 _____       ________ / ___ |Tony Jambu, Database Consultant
  /_  _        /_ __ /      |Wizard Consulting,Aust (ACN 065934778)
 /(_)/ )(_/ \_/(///(/_)/_(  |CIS: 100250.2003_at_compuserve.com FAX: +61-3-4163559
 \_______/                  |EMAIL:TJambu_at_wizard.com.au PHONE: +61-3-4122905

Received on Wed Aug 23 1995 - 00:00:00 CEST