Re: script for database schema

From: Tony Jambu <aaj_at_cmutual.com.au>
Date: Tue, 28 Jun 1994 14:10:43 GMT
Message-ID: <Cs421w.Fxt_at_cmutual.com.au>

Try the following program/steps.

1. exp system/manager full=y file=exp.dmp
2. imp system/manager file=exp.dmp show=y | impl2sql > rebuild.sql

You should then be able to edit and execute the rebuild.sql script.

I am enclosing the program/script impl2sql for your use. This script and others will be presented to IOUG for distribution and also at my presentation at IOUW94.

PS: Thanks to David Bath for his expertise and knowledge of sed.

Start of script:

#!/bin/sh
#
# NAME
# impl2sql - _at_(#)Generate SQL script from Oracle V6 dump
# - Requires the UNIX or GNU dos 'sed' program
# SYNOPSIS
# sh: imp / rows=n file=expdat.dmp show=y 2>&1 | impl2sql > implist.sql
# csh: imp system/ballondvl rows=n show=y file=expdat.dmp |& \
# impl2sql > implist.sql
# DESCRIPTION
# First of all, create a log file from the output of Oracle
# 6.0.XX 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 impl2sql 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 fully been tested.
#
# Comments with brackets in them will be uglified.
#
# No manual page yet - too lazy
#
# Versions of Oracle import other than that distributed with
# v6.0.36 (UNIX) have NOT been tested. This refers to the
# executable, NOT the export file itself.
# AUTHOR
# David T. Bath 1991
# Modified :
# Tony Jambu 1992,1993 (TJambu_at_cmutual.com.au)
#

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 CONNECT\)/;\
\1/' \

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    -e 's/^ "\(COMMENT ON \)/;\
\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 '/"$/{' -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 '/"$/{' -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 '/"$/{' -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 '/"$/{' -e 'N' -e 's/"\n  "//' -e 's/"\n;/;/' -e '}' \
    -e '/"$/{' -e 'N' -e 's/"\n  "//' -e 's/"\n;/;/' -e '}' \
    -e 's/ WHERE / \

WHERE /g' \

    -e 's/ AND / \
  AND /g' \
    -e 's/, "/, \
"/g' \

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

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

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

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

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

----------------------------------END-------------------------------

ta
tony

-- 
 _____       ________ / ____ |Tony Jambu, Database Administrator
  /_  _        /_ __ /       |Colonial Mutual Invest Mgmt,Aust (ACN004021809)
 /(_)/ )(_/ \_/(///(/_)/_(   |EMAIL: TJambu_at_cmutual.com.au
 \_______/                   |PHONE: +61-3-2831639       FAX: +61-3-2831090

Received on Tue Jun 28 1994 - 16:10:43 CEST