Message-Id: <10682.122319@fatcity.com> From: Alex Hillman Date: Thu, 16 Nov 2000 16:45:52 -0500 Subject: RE: Exp/Imp and old storage clauses This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C05016.9D345650 Content-Type: text/plain; charset="iso-8859-1" Much easier is to export import without any changes and then in 8.1.6 database you can use move alter table ... move with desired storage parameters and also you can rebuild indexes. I used EZSQL for that (moving and rebuilding storage) - works like a charm. Alex Hillman -----Original Message----- From: Fowler, Kenneth R [mailto:kenneth_r_fowler@groton.pfizer.com] Sent: Thursday, November 16, 2000 3:37 PM To: Multiple recipients of list ORACLE-L Subject: Exp/Imp and old storage clauses List, I have been migrating a few database to 8.1.6 (from 7.n.n) on Solaris lately and when I do this I have been using exp/imp so that I can apply some of the storage strategies outlined in the "How To Stop Defragmenting and Start Living" document. The basic method I am using is... 1. Create new empty database that have tablespaces with appropriate default storage clauses (initial extent, next extent as per the doc's suggestions etc etc). 2. Perform full export of old database. 3. Use imp .... indexfile = filename.sql to extract sql for tables, indexes and constraints. 4. Use a nasty sed script I put together to process the above extracted sql to rip out all of the storage clauses defined at object level and change tablespace clause to whatever I want etc 5. Create the tables from sql generated in step 3 and 4. 6. Import row data into the preexisting tables 7. Create indexes, constraints from sql generated in step 3 and 4. 8. Perform a final import (ignore = y) to import grants and any other stuff I may have missed (should hopefully not be anything). This is quite tedious, especially all of the checking I need to do to verify that the sed script works as desired. Anyone know of an easier/better way to import objects and default to the tablespace storage clause (that is, leave off the object storage clause!). I would really love import to have a switch that would do this for me. Anyone else find themselves jumping through the same hoops????? Ken (Kenneth R Fowler) (860) 732 0026 (Voice) (860) 732 3689 (Fax) (860) 715 8346 (Fax to Email) (860) 599 8791 (Fax @ Home) Clinical Systems DBA Support Kenneth_R_Fowler@groton.pfizer.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fowler, Kenneth R INET: kenneth_r_fowler@groton.pfizer.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ------_=_NextPart_001_01C05016.9D345650 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: Exp/Imp and old storage clauses

Much easier is to export import without any changes = and then in 8.1.6 database you can use move alter table ... move with = desired storage parameters and also you can rebuild indexes.

I used EZSQL for that (moving and rebuilding storage) = - works like a charm.

Alex Hillman

-----Original Message-----
From: Fowler, Kenneth R [mailto:kenneth_r_fowl= er@groton.pfizer.com]
Sent: Thursday, November 16, 2000 3:37 PM
To: Multiple recipients of list ORACLE-L
Subject: Exp/Imp and old storage clauses


List,


I have been migrating a few database to 8.1.6 (from = 7.n.n) on Solaris lately
and when I do this I have been using exp/imp so that = I can apply some of the
storage strategies outlined in the "How To Stop = Defragmenting and Start
Living" document.  The basic method I am = using is...

1.  Create new empty database that have = tablespaces with appropriate default
storage clauses (initial extent, next extent as per = the doc's suggestions
etc etc).

2.  Perform full export of old database.

3.  Use imp .... indexfile =3D filename.sql to = extract sql for tables, indexes
and constraints.

4.  Use a nasty sed script  I put together = to process the above extracted
sql to rip out all of the storage clauses defined at = object level and change
tablespace clause to whatever I want etc

5.  Create the tables from sql generated in step = 3 and 4.

6.  Import row data into the preexisting = tables

7.  Create indexes, constraints from sql = generated in step 3 and 4.

8.  Perform a final import (ignore =3D y) to = import grants and any other stuff
I may have missed (should hopefully not be = anything).


This is quite tedious, especially all of the checking = I need to do to verify
that the sed script works as desired.  Anyone = know of an easier/better way
to import objects and default to the tablespace = storage clause (that is,
leave off the object storage clause!).

I would really love import to have a switch that = would do this for me.
Anyone else find themselves jumping through the same = hoops?????



        Ken
        (Kenneth = R Fowler)
        (860) 732 = 0026 (Voice)
        (860) 732 = 3689 (Fax)
        (860) 715 = 8346 (Fax to Email)
        (860) 599 = 8791 (Fax @ Home)
        Clinical = Systems DBA Support
        Kenneth_R_Fowler@groton.pfizer.com


--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Fowler, Kenneth R
  INET: = kenneth_r_fowler@groton.pfizer.com

Fat City Network Services    -- (858) = 538-5051  FAX: (858) 538-5051
San Diego, = California        -- Public Internet = access / Mailing Lists
---------------------------------------------------------------= -----
To REMOVE yourself from this mailing list, send an = E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of = 'ListGuru') and in
the message BODY, include a line containing: UNSUB = ORACLE-L
(or the name of mailing list you want to be removed =