Re: export/import to a smaller database

From: Connor McDonald <mcdonald.connor.cs_at_bhp.com.au>
Date: Thu, 07 Jan 1999 17:15:05 +0800
Message-ID: <36947B19.71DB@bhp.com.au>

Pat Soehl wrote:
>
> Oracle 7.3.4, sun solaris
>
> This feels like a dumb question, but I feel
> compelled to ask it anyway.
>
> I have a large database that was initially sized
> for projected growth. Currently it has only a
> small number of rows compared to what it will have.
>
> Our testing department wants to create a logical
> copy of this database which contains the current
> data. Due to size constraints they want to use a
> smaller database, since the current data will
> easily fit in it.
>
> I did an export full=y row=n to get the ddl
> pulled out.
>
> I'm running into difficulty with the export files,
> since it wants to create tablespaces, tables,
> indexes and many other objects which all seem to
> have size parameters specified. I created the
> tablespaces after a lot of editing with smaller
> sizes, but now have hundreds of objects that need
> to be edited before I can even create tables and
> indexes. Any ideas on an easy way to do this? That
> is, export a larger database into a smaller one?
>
> Any scripts on editing the ddl to modify or remove
> the size specifications?
>
> Thanks in advance,

A quick hack...

(Assuming Unix)...

Run strings on your export file (with rows=n) to get the ascii data out.
grep out your "CREATE TABLE", "CREATE INDEX", "ALTER TABLE ... PRIMARY KEY" statements
with this reduced set, search for INITIAL, NEXT etc, use sed or awk to change the sizes down

Clunky I know...

HTH --

Connor McDonald
BHP Information Technology
Perth, Western Australia
"The difference between me and a madman is that I am not mad"

Salvador Dali

Received on Thu Jan 07 1999 - 03:15:05 CST