| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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_at_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...
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_at_groton.pfizer.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fowler, Kenneth R INET: kenneth_r_fowler_at_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_at_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-printableReceived on Thu Nov 16 2000 - 15:45:52 CST
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2448.0">
<TITLE>RE: Exp/Imp and old storage clauses</TITLE>
</HEAD>
<BODY>
<P><FONT SIZE=3D2>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.</FONT></P>
<P><FONT SIZE=3D2>I used EZSQL for that (moving and rebuilding storage) =
- works like a charm.</FONT>
</P>
<P><FONT SIZE=3D2>Alex Hillman</FONT>
</P>
<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: Fowler, Kenneth R [<A =
HREF=3D"mailto:kenneth_r_fowler_at_groton.pfizer.com">mailto:kenneth_r_fowl= er_at_groton.pfizer.com</A>]</FONT>
<BR><FONT SIZE=3D2>Sent: Thursday, November 16, 2000 3:37 PM</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=3D2>Subject: Exp/Imp and old storage clauses</FONT>
</P>
<BR>
<P><FONT SIZE=3D2>List,</FONT>
</P>
<BR>
<P><FONT SIZE=3D2>I have been migrating a few database to 8.1.6 (from =
7.n.n) on Solaris lately</FONT>
<BR><FONT SIZE=3D2>and when I do this I have been using exp/imp so that =
I can apply some of the</FONT>
<BR><FONT SIZE=3D2>storage strategies outlined in the "How To Stop =
Defragmenting and Start</FONT>
<BR><FONT SIZE=3D2>Living" document. The basic method I am =
using is...</FONT>
</P>
<P><FONT SIZE=3D2>1. Create new empty database that have =
tablespaces with appropriate default</FONT>
<BR><FONT SIZE=3D2>storage clauses (initial extent, next extent as per =
the doc's suggestions</FONT>
<BR><FONT SIZE=3D2>etc etc).</FONT>
</P>
<P><FONT SIZE=3D2>2. Perform full export of old database.</FONT>
</P>
<P><FONT SIZE=3D2>3. Use imp .... indexfile =3D filename.sql to =
extract sql for tables, indexes</FONT>
<BR><FONT SIZE=3D2>and constraints.</FONT>
</P>
<P><FONT SIZE=3D2>4. Use a nasty sed script I put together =
to process the above extracted</FONT>
<BR><FONT SIZE=3D2>sql to rip out all of the storage clauses defined at =
object level and change</FONT>
<BR><FONT SIZE=3D2>tablespace clause to whatever I want etc</FONT>
</P>
<P><FONT SIZE=3D2>5. Create the tables from sql generated in step =
3 and 4.</FONT>
</P>
<P><FONT SIZE=3D2>6. Import row data into the preexisting =
tables</FONT>
</P>
<P><FONT SIZE=3D2>7. Create indexes, constraints from sql =
generated in step 3 and 4.</FONT>
</P>
<P><FONT SIZE=3D2>8. Perform a final import (ignore =3D y) to =
import grants and any other stuff</FONT>
<BR><FONT SIZE=3D2>I may have missed (should hopefully not be =
anything).</FONT>
</P>
<BR>
<P><FONT SIZE=3D2>This is quite tedious, especially all of the checking =
I need to do to verify</FONT>
<BR><FONT SIZE=3D2>that the sed script works as desired. Anyone =
know of an easier/better way</FONT>
<BR><FONT SIZE=3D2>to import objects and default to the tablespace =
storage clause (that is,</FONT>
<BR><FONT SIZE=3D2>leave off the object storage clause!).</FONT>
</P>
<P><FONT SIZE=3D2>I would really love import to have a switch that =
would do this for me.</FONT>
<BR><FONT SIZE=3D2>Anyone else find themselves jumping through the same =
hoops?????</FONT>
</P>
<BR>
<BR>
<P> <FONT SIZE=3D2>Ken</FONT>
<BR> <FONT SIZE=3D2>(Kenneth =
R Fowler)</FONT>
<BR> <FONT SIZE=3D2>(860) 732 =
0026 (Voice)</FONT>
<BR> <FONT SIZE=3D2>(860) 732 =
3689 (Fax)</FONT>
<BR> <FONT SIZE=3D2>(860) 715 =
8346 (Fax to Email)</FONT>
<BR> <FONT SIZE=3D2>(860) 599 =
8791 (Fax @ Home)</FONT>
<BR> <FONT SIZE=3D2>Clinical =
Systems DBA Support</FONT>
<BR> <FONT =
SIZE=3D2>Kenneth_R_Fowler_at_groton.pfizer.com</FONT>
</P>
<BR>
<P><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Please see the official ORACLE-L FAQ: <A =
HREF=3D"http://www.orafaq.com" = TARGET=3D"_blank">http://www.orafaq.com</A></FONT>
<BR><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Author: Fowler, Kenneth R</FONT>
<BR><FONT SIZE=3D2> INET: =
kenneth_r_fowler_at_groton.pfizer.com</FONT>
</P>
<P><FONT SIZE=3D2>Fat City Network Services -- (858) =
538-5051 FAX: (858) 538-5051</FONT>
<BR><FONT SIZE=3D2>San Diego, =
California -- Public Internet = access / Mailing Lists</FONT>
<BR><FONT =
SIZE=3D2>---------------------------------------------------------------= -----</FONT>
<BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an =
E-Mail message</FONT>
<BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of =
'ListGuru') and in</FONT>
<BR><FONT SIZE=3D2>the message BODY, include a line containing: UNSUB =
ORACLE-L</FONT>
<BR><FONT SIZE=3D2>(or the name of mailing list you want to be removed =
 |
 |